---
title: "pk80001_legalnet stored procedure documentation"
summary: "Analysis of Oracle package pk80001_legalnet (WCIS_DBA), the LegalNet data-access package backing legal case, hearing schedule, appeal, case outcome, case note, case cost, file location, mail-date, consolidation, and attorney profile/schedule CRUD operations."
status: draft
owner: SAIF Documentation Generator
last_reviewed: 2026-08-31
source_schema: "WCIS_DBA"
source_name: "pk80001_legalnet"
source_type: "PACKAGE"
retrieval_status: "found"
retrieval_command: "legends oracle query --owner SAIF --name pk80001_legalnet"
retrieval_date: "2026-08-31"
---

# pk80001_legalnet

## Overview

| Field | Value |
| --- | --- |
| **Schema** | WCIS_DBA |
| **Type** | Package |
| **Database** | WCIS_DBA / Oracle |
| **Calling libraries** | `SAIF.Business.Claim.Legal` |
| **Calling classes** | `clsCaseNote`, `clsCaseSearch`, `clsConsolidations`, `clsCreateFile`, `clsAppeal`, `clsAppealExtension`, `clsCaseOutcome`, `clsLegalCase`, `clsLegalSchedule`, `clsHearingLocationInfo`, `clsAcknowledgeList`, `clsAttorneyProfile`, `clsBoardLog`, `clsLegalFileLocation`, `clsLegalCaseEvaluation`, `clsAttorneyWorkerAssignment`, `clsMailDate`, `clsImageAlerts`, `mdlCodeTables.clsLegalStaffList` |
| **Member count** | 98 (55 members previously catalogued from referencing .NET code, plus 43 additional members present in the retrieved package spec that have no confirmed .NET call site*) |

Package name casing is inconsistent across the source (`pk80001_legalnet`, `Pk80001_Legalnet`, `pk80001_legalNet`, `PK80001_LEGALNET`). Oracle identifiers are case-insensitive when unquoted, so these are all the same package; names below are normalised to the casing declared in the retrieved spec.

## Purpose

`pk80001_legalnet` is the primary Oracle PL/SQL package behind SAIF's LegalNet module. It implements CRUD and lookup logic for legal cases (`LEGAL_CASE`), hearing schedules (`SCHEDULE`), appeals and appeal extensions (`APPEAL`, `APPEAL_EXTENSION`), case outcomes and their issues/staff (`CASE_OUTCOME`, `OUTCOME_ISSUE`, `OUTCOME_STAFF`), case notes (`CASE_NOTE`), case costs (`CASE_COST`), legal file locations (`LEGAL_FILE_LOCATION`), case consolidation (`CONSOLIDATED_CASE`), attorney profiles and schedules (`ATTORNEY_PROFILE`, `ATTORNEY_SCHEDULE`), legal case employer/other-firm associations (`LEGAL_CASE_EMPLOYER`, `OTHER_FIRM`), mail-date/OAR/reconsideration-letter tracking, acknowledgement letters, board logs, letter requests, and image-alert history. It also derives case-search results, assigned-attorney lookups, and hearing-sequence-number bookkeeping used throughout the LegalNet UI and related batch jobs.

## Procedures / Methods

### findCase

| Field | Value |
| --- | --- |
| **Returns** | `recordset` (REF CURSOR) |
| **Called by** | `clsCaseSearch` |

**Description:** Looks up a single legal case by an arbitrary case-identifier string, trying identifier types in priority order: WCB number first; if the input is 7 characters or fewer, DCBS number next; then OWCP number; then OALJ number; then BRB number; and finally the appeal number as a last resort. Returns the matching `LEGAL_CASE` row(s) via `recordset`.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `recordset` | OUT | REF CURSOR | — | Matching legal case row(s). |
| `in_num` | IN | VARCHAR2 | — | Case identifier to resolve (WCB/DCBS/OWCP/OALJ/BRB/appeal number). |

### get_hearing_date

| Field | Value |
| --- | --- |
| **Returns** | DATE |
| **Called by** | No confirmed .NET call site* |

**Description:** Returns the scheduled hearing date for the given legal case's most recent hearing schedule row.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_case_seq` | IN | `LEGAL_CASE.legal_case_seq_num%TYPE` | — | Legal case sequence number. |

### get_consolidated_hearing_seq

| Field | Value |
| --- | --- |
| **Returns** | NUMBER |
| **Called by** | No confirmed .NET call site* |

**Description:** Resolves the most-recent HEAR schedule sequence number for a case's consolidation primary, used by `updateHearingSeqNum` when a case is a secondary in a consolidation.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_case_seq` | IN | `LEGAL_CASE.legal_case_seq_num%TYPE` | — | Legal case sequence number. |

### get_hearingID

| Field | Value |
| --- | --- |
| **Returns** | NUMBER |
| **Called by** | `clsHearingLocationInfo` |

**Description:** Returns the schedule sequence number of the case's most recent hearing, used to look up hearing-location detail.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_case_seq` | IN | `LEGAL_CASE.legal_case_seq_num%TYPE` | — | Legal case sequence number. |

### get_asgn_atty

| Field | Value |
| --- | --- |
| **Returns** | VARCHAR2 |
| **Called by** | `clsLegalCase` |

**Description:** Resolves the assigned attorney's display name for a case using a three-tier fallback: (1) the attorney on the case's most recent appeal, (2) the attorney assigned to the case's scheduled hearing, (3) the assigned other-firm name; returns an empty string if none is found ([BR-PKG1-005](../business-rules.md#br-pkg1-005-assigned-attorney-resolution-fallback-order)).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `IN_case_Seq_num` | IN | `LEGAL_CASE.legal_case_seq_num%TYPE` | — | Legal case sequence number. |

### get_asgn_atty_id

| Field | Value |
| --- | --- |
| **Returns** | VARCHAR2 |
| **Called by** | No confirmed .NET call site* |

**Description:** Resolves the assigned attorney's employee/person identifier using the same three-tier fallback order as `get_asgn_atty` ([BR-PKG1-005](../business-rules.md#br-pkg1-005-assigned-attorney-resolution-fallback-order)), returning the identifier rather than the display name.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `IN_case_Seq_num` | IN | `LEGAL_CASE.legal_case_seq_num%TYPE` | — | Legal case sequence number. |

### get_atty_mngr_email

| Field | Value |
| --- | --- |
| **Returns** | VARCHAR2 |
| **Called by** | No confirmed .NET call site* |

**Description:** Returns the email address of the manager of the attorney assigned to the case, used for notification routing.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `aLegalCaseNum` | IN | NUMBER | — | Legal case sequence number. |

### get_mailid_for_emplid

| Field | Value |
| --- | --- |
| **Returns** | VARCHAR2 |
| **Called by** | No confirmed .NET call site* |

**Description:** Translates an employee ID into the corresponding mail/user ID (`adp_employee.user_id`).*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `inEmplid` | IN | VARCHAR2 | — | Employee ID to translate. |

### get_case_num

| Field | Value |
| --- | --- |
| **Returns** | VARCHAR2 |
| **Called by** | No confirmed .NET call site* |

**Description:** Resolves a single display case number for the given legal case by taking the first non-null value in priority order: WCB number, DCBS number, OWCP number, OALJ number, then BRB number ([BR-PKG1-006](../business-rules.md#br-pkg1-006-case-number-display-priority)).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `aLegalCaseNum` | IN | NUMBER | — | Legal case sequence number. |

### InsertLegalCase

| Field | Value |
| --- | --- |
| **Returns** | NUMBER (new `legal_case_seq_num`) |
| **Called by** | `clsLegalCase` |

**Description:** Inserts a new `LEGAL_CASE` row from the full set of case attributes (request type, WCB/DCBS/OWCP/OALJ/BRB numbers, case status, attorney assignment, OAR/acknowledge/DEF dates, stress-claim flag, consolidation flag, etc.) and returns the new sequence number.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `LegalCaseSeqNum` | IN | `LEGAL_CASE.LEGAL_CASE_SEQ_NUM%TYPE` | — | Sequence number to assign to the new case. |
| `CaseTypeCode` | IN | `LEGAL_CASE.REQUEST_TYPE_CODE%TYPE` | — | Case type code. |
| `RequestTypeCode` | IN | `LEGAL_CASE.REQUEST_TYPE_CODE%TYPE` | — | Request type code. |
| `WcbNum` | IN | `LEGAL_CASE.WCB_NUM%TYPE` | — | Workers' Compensation Board case number. |
| `DcbsNum` | IN | `LEGAL_CASE.DCBS_NUM%TYPE` | — | DCBS case number. |
| `CaseStatusCode` | IN | `LEGAL_CASE.CASE_STATUS_CODE%TYPE` | — | Case status code. |
| `claimID` | IN | `LEGAL_CASE.claim_id%TYPE` | — | Associated claim ID. |
| `RequestDate` | IN | `LEGAL_CASE.request_date%TYPE` | — | Legal request date. |
| `WorkerAttyProvLocID` | IN | `LEGAL_CASE.WORKER_ATTY_PROV_LOC_ID%TYPE` | — | Worker's attorney provider location ID. |
| `WorkerAttyFirmProvLocID` | IN | `LEGAL_CASE.WORKER_ATTY_FIRM_PROV_LOC_ID%TYPE` | — | Worker's attorney firm provider location ID. |
| `OarDueDate` | IN | `LEGAL_CASE.OAR_DUE_DATE%TYPE` | — | Order allowing/reconsideration due date. |
| `OarMailedDate` | IN | `LEGAL_CASE.OAR_MAILED_DATE%TYPE` | — | OAR mailed date. |
| `LegalFileCreateDate` | IN | `LEGAL_CASE.LEGAL_FILE_CREATE_DATE%TYPE` | — | Legal file creation date. |
| `CaseWeightValue` | IN | `LEGAL_CASE.CASE_WEIGHT_VALUE%TYPE` | — | Case weighting value ([BR-PKG1-007](../business-rules.md#br-pkg1-007-oar-due-date-and-case-weight-calculation)). |
| `AcknowledgeDate` | IN | `LEGAL_CASE.ACKNOWLEDGE_DATE%TYPE` | — | Acknowledgement date. |
| `RequestNewWcbNumFlag` | IN | `LEGAL_CASE.REQUEST_ALL_DAY_HEARING_FLAG%TYPE` | — | Flag requesting a new WCB number. |
| `RequestedByCode` | IN | `LEGAL_CASE.REQUESTED_BY_CODE%TYPE` | — | Code identifying who requested the case. |
| `RequestReceiptDate` | IN | `LEGAL_CASE.REQUEST_RECEIPT_DATE%TYPE` | — | Date the request was received. |
| `RequestPostmarkDate` | IN | `LEGAL_CASE.REQUEST_POSTMARK_DATE%TYPE` | — | Postmark date on the request. |
| `RequestAllDayHearingFlag` | IN | `LEGAL_CASE.REQUEST_ALL_DAY_HEARING_FLAG%TYPE` | — | All-day-hearing request flag. |
| `StressClaimFlag` | IN | `LEGAL_CASE.STRESS_CLAIM_FLAG%TYPE` | — | Stress-claim flag ([BR-PKG1-007](../business-rules.md#br-pkg1-007-oar-due-date-and-case-weight-calculation)). |
| `ReconLetterMailedDate` | IN | `LEGAL_CASE.RECON_LETTER_MAILED_DATE%TYPE` | — | Reconsideration letter mailed date. |
| `ReconPacketReceivedDate` | IN | `LEGAL_CASE.RECON_PACKET_RECEIVED_DATE%TYPE` | — | Reconsideration packet received date. |
| `DismissalDate` | IN | `LEGAL_CASE.DISMISSAL_DATE%TYPE` | — | Dismissal date. |
| `SettlementDate` | IN | `LEGAL_CASE.SETTLEMENT_DATE%TYPE` | — | Settlement date. |
| `SendDefFlag` | IN | `LEGAL_CASE.SEND_DEF_FLAG%TYPE` | — | Flag to send DEF letter. |
| `DefSentDate` | IN | `LEGAL_CASE.DEF_SENT_DATE%TYPE` | — | DEF letter sent date. |
| `EmployerEvaluationNum` | IN | `LEGAL_CASE.EMPLOYER_EVALUATION_NUM%TYPE` | — | Employer evaluation number. |
| `ConsolidateCaseFlag` | IN | `LEGAL_CASE.CONSOLIDATE_CASE_FLAG%TYPE` | — | Flag indicating case participates in consolidation. |
| `OwcpNum` | IN | `LEGAL_CASE.OWCP_NUM%TYPE` | — | OWCP case number. |
| `OaljNum` | IN | `LEGAL_CASE.OALJ_NUM%TYPE` | — | OALJ case number. |
| `BrbNum` | IN | `LEGAL_CASE.BRB_NUM%TYPE` | — | BRB case number. |
| `CreateDate` | IN | `LEGAL_CASE.CREATE_DATE%TYPE` | — | Audit creation timestamp. |
| `CreateUser` | IN | `LEGAL_CASE.CREATE_USER%TYPE` | — | Audit creation user. |
| `MostRecentHearingSeqNum` | IN | `LEGAL_CASE.MOST_RECENT_HEARING_SEQ_NUM%TYPE` | — | Most recent hearing schedule sequence number. |
| `ClaimImageAlertsFlag` | IN | `LEGAL_CASE.CLAIM_IMAGE_ALERTS_FLAG%TYPE` | — | Claim image-alerts flag. |

### UpdateLegalCase

| Field | Value |
| --- | --- |
| **Returns** | NUMBER (rows-affected/status indicator) |
| **Called by** | `clsLegalCase` |

**Description:** Updates an existing `LEGAL_CASE` row with the same full set of attributes as `InsertLegalCase`, replacing `CreateDate`/`CreateUser` with `UpdateDate`/`UpdateUser`.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `LegalCaseSeqNum` | IN | `LEGAL_CASE.LEGAL_CASE_SEQ_NUM%TYPE` | — | Case to update. |
| `CaseTypeCode` through `ClaimImageAlertsFlag` | IN | (see `InsertLegalCase`) | — | Same attribute set as `InsertLegalCase`, with `UpdateDate`/`UpdateUser` replacing `CreateDate`/`CreateUser`. |

### CREATE_REQUEST_ISSUE

| Field | Value |
| --- | --- |
| **Returns** | none (procedure) |
| **Called by** | No confirmed .NET call site* |

**Description:** Inserts a `REQUEST_ISSUE` row recording the issue code/text associated with a legal case request; used by `UPDATE_LEGALCASE_REQUEST` when tracking reconsideration issues ([BR-PKG1-007](../business-rules.md#br-pkg1-007-oar-due-date-and-case-weight-calculation)).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_legal_case_seq` | IN | `LEGAL_CASE.legal_Case_seq_num%TYPE` | — | Legal case sequence number. |
| `in_issue_code` | IN | `REQUEST_ISSUE.REQUEST_ISSUE_CODE%TYPE` | — | Request issue code. |
| `in_issue_text` | IN | `REQUEST_ISSUE.REQUEST_ISSUE_TEXT%TYPE` | — | Request issue free text. |

### get_case_issue_codes

| Field | Value |
| --- | --- |
| **Returns** | VARCHAR2 (delimited list of codes) |
| **Called by** | No confirmed .NET call site* |

**Description:** Returns a concatenated list of `REQUEST_ISSUE` codes recorded for the given case.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_case_seq_num` | IN | NUMBER | — | Legal case sequence number. |

### get_case_issue_text

| Field | Value |
| --- | --- |
| **Returns** | VARCHAR2 (concatenated text) |
| **Called by** | `clsLegalCase` |

**Description:** Returns the concatenated free-text issue descriptions recorded for the given case's request issues.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_case_seq_num` | IN | NUMBER | — | Legal case sequence number. |

### Casesearch

| Field | Value |
| --- | --- |
| **Returns** | `recordset` (`search_cursor` REF CURSOR of `search_rec`: WCB number, case status, attorney name, hearing date, request issue, requested-by, case seq num, claim ID) |
| **Called by** | `clsCaseSearch` |

**Description:** Searches legal cases for a comma-delimited list of claim IDs, applying a closed-case filter: when `includeClosed = 'Y'` no cases are excluded by status, otherwise cases with `CASE_STATUS_CODE = 'CLOSE'` are excluded ([BR-PKG1-001](../business-rules.md#br-pkg1-001-case-search-excludes-closed-cases-by-default)). This is the single procedure backing the legacy `CaseSearch`/`findCase` .NET call sites.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `recordset` | OUT | `search_cursor` | — | Matching case search rows. |
| `claimIdsCommaDelimited` | IN | VARCHAR2 | — | Comma-delimited list of claim IDs to search. |
| `includeClosed` | IN | VARCHAR2 | — | `'Y'` to include closed cases, otherwise closed cases are excluded. |

### updateCaseStatus

| Field | Value |
| --- | --- |
| **Returns** | none (procedure) |
| **Called by** | `clsConsolidations` |

**Description:** Updates a legal case's status code and, because a status change on a consolidation primary must be reflected on its secondary cases, cascades the same status update to all secondary cases consolidated under the given case ([BR-PKG1-002](../business-rules.md#br-pkg1-002-case-status-changes-cascade-to-consolidated-secondary-cases)).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `aLegalCaseNum` | IN | NUMBER | — | Legal case sequence number. |
| `newStatus` | IN | VARCHAR2 | — | New case status code. |
| `updateDate` | IN | DATE | — | Audit update timestamp. |
| `updateUser` | IN | VARCHAR2 | — | Audit update user. |

### updateHearingSeqNum

| Field | Value |
| --- | --- |
| **Returns** | none (procedure) |
| **Called by** | `clsLegalCase` |

**Description:** Recomputes and stores `LEGAL_CASE.MOST_RECENT_HEARING_SEQ_NUM` using a three-tier fallback: (1) if the case is a consolidated secondary, use its primary's most recent HEAR schedule via `get_consolidated_hearing_seq`; (2) otherwise use the case's own most recent HEAR schedule; (3) if neither exists, fall back to the case's own most recent CCALL/INCON/MED schedule. The recalculated value then cascades to any consolidated secondary cases ([BR-PKG1-003](../business-rules.md#br-pkg1-003-hearing-sequence-number-fallback-order)).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `aLegalCaseNum` | IN | NUMBER | — | Legal case sequence number to recompute. |

### getLCE

| Field | Value |
| --- | --- |
| **Returns** | `Recordset` (REF CURSOR) |
| **Called by** | `clsLegalCaseEvaluation` |

**Description:** Returns the legal case evaluation (LCE) row(s) for a given legal case.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `LegalCaseID` | IN | NUMBER | — | Legal case sequence number. |
| `Recordset` | OUT | REF CURSOR | — | LCE row(s). |

### updateLCE

| Field | Value |
| --- | --- |
| **Returns** | NUMBER (status indicator) |
| **Called by** | `clsLegalCaseEvaluation` |

**Description:** Updates an existing LCE row's excused flag, due/filed dates, adjuster, and case-transfer date.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `EvaluationID` | IN | NUMBER | — | LCE sequence number to update. |
| `IsExcused` | IN | VARCHAR2 | — | Excused flag. |
| `dueDate` | IN | DATE | — | Evaluation due date. |
| `filedDate` | IN | DATE | — | Evaluation filed date. |
| `AdjusterEmployeeID` | IN | VARCHAR2 | — | Adjuster's employee ID. |
| `casetransferDate` | IN | DATE | — | Case transfer date. |
| `updateDate` | IN | DATE | — | Audit update timestamp. |
| `updateUser` | IN | VARCHAR2 | — | Audit update user. |
| `AsgnAttyEmplid` | IN | VARCHAR2 | — | Assigned attorney employee ID. |

### insertLCE

| Field | Value |
| --- | --- |
| **Returns** | NUMBER (status indicator) |
| **Called by** | `clsLegalCaseEvaluation` |

**Description:** Inserts a new LCE row for a legal case. Wraps the insert in a `WHEN OTHERS` handler that re-raises via `RAISE_APPLICATION_ERROR(-20004, <message>)` on failure.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `EvaluationID` | IN | NUMBER | — | New LCE sequence number. |
| `LegalCaseID` | IN | NUMBER | — | Legal case sequence number. |
| `IsExcused` | IN | VARCHAR2 | — | Excused flag. |
| `dueDate` | IN | DATE | — | Evaluation due date. |
| `filedDate` | IN | DATE | — | Evaluation filed date. |
| `AdjusterEmployeeID` | IN | VARCHAR2 | — | Adjuster's employee ID. |
| `casetransferDate` | IN | DATE | — | Case transfer date. |
| `createDate` | IN | DATE | — | Audit creation timestamp. |
| `createUser` | IN | VARCHAR2 | — | Audit creation user. |
| `AsgnAttyEmplid` | IN | VARCHAR2 | — | Assigned attorney employee ID. |

### getSchedule

| Field | Value |
| --- | --- |
| **Returns** | `recordset` (REF CURSOR) |
| **Called by** | `clsLegalSchedule` |

**Description:** Returns all hearing schedule rows for a legal case.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `LegalCaseSeqNum` | IN | NUMBER | — | Legal case sequence number. |
| `recordset` | OUT | REF CURSOR | — | Schedule row(s). |

### updateSchedule

| Field | Value |
| --- | --- |
| **Returns** | NUMBER (status indicator) |
| **Called by** | `clsLegalSchedule` |

**Description:** Updates an existing `SCHEDULE` row (date/time, ALJ, hearing location, assigned attorney, duration, type/status codes, reminder date, assigned other-firm/person) and triggers recalculation of the case's hearing sequence number.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `ScheduleSeqNum` | IN | NUMBER | — | Schedule row to update. |
| `ScheduleDate` | IN | DATE | — | Hearing date. |
| `ScheduleTime` | IN | VARCHAR2 | — | Hearing time. |
| `AljPersonSeqNum` | IN | NUMBER | — | Assigned ALJ person sequence number. |
| `HearingLocationSeqNum` | IN | NUMBER | — | Hearing location sequence number. |
| `AsgnAttorneyEmplid` | IN | VARCHAR2 | — | Assigned attorney employee ID. |
| `DurationCode` | IN | VARCHAR2 | — | Hearing duration code. |
| `ScheduleTypeCode` | IN | VARCHAR2 | — | Schedule type code (e.g. HEAR/CCALL/INCON/MED). |
| `ScheduleStatusCode` | IN | VARCHAR2 | — | Schedule status code. |
| `KeepAttorneyFlag` | IN | VARCHAR2 | — | Flag to retain attorney assignment. |
| `AssignedDate` | IN | DATE | — | Assignment date. |
| `ReminderDate` | IN | DATE | — | Reminder date. |
| `ScheduleText` | IN | VARCHAR2 | — | Free-text schedule notes. |
| `UpdateDate` | IN | DATE | — | Audit update timestamp. |
| `UpdateUser` | IN | VARCHAR2 | — | Audit update user. |
| `AsgnOtherFirmSeqNum` | IN | NUMBER | — | Assigned other-firm sequence number. |
| `AsgnPersonSeqNum` | IN | NUMBER | — | Assigned person sequence number. |

### insertSchedule

| Field | Value |
| --- | --- |
| **Returns** | NUMBER (status indicator) |
| **Called by** | `clsLegalSchedule` |

**Description:** Inserts a new `SCHEDULE` row for a legal case with the same attribute set as `updateSchedule`, and triggers recalculation of the case's hearing sequence number.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `ScheduleSeqNum` | IN | NUMBER | — | New schedule sequence number. |
| `LegalCaseSeqNum` | IN | NUMBER | — | Legal case sequence number. |
| `ScheduleDate` through `asgnPersonSeqNum` | IN | (see `updateSchedule`) | — | Same attribute set as `updateSchedule`, with `createDate`/`createUser` replacing `UpdateDate`/`UpdateUser`. |

### deleteSchedule

| Field | Value |
| --- | --- |
| **Returns** | NUMBER (status indicator) |
| **Called by** | `clsLegalSchedule` |

**Description:** Deletes a `SCHEDULE` row and triggers recalculation of the owning case's hearing sequence number.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `ScheduleSeqNum` | IN | NUMBER | — | Schedule row to delete. |

### getLegalCase

| Field | Value |
| --- | --- |
| **Returns** | `recordset` (IN OUT REF CURSOR) |
| **Called by** | `clsLegalCase` |

**Description:** Returns the `LEGAL_CASE` row for a given legal case sequence number and/or claim ID.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `LegalCaseSeqNum` | IN | NUMBER | — | Legal case sequence number. |
| `ClaimId` | IN | NUMBER | — | Associated claim ID. |
| `recordset` | IN OUT | REF CURSOR | — | Legal case row. |

### getClaimImageAlertsUpdateDate

| Field | Value |
| --- | --- |
| **Returns** | DATE |
| **Called by** | No confirmed .NET call site* |

**Description:** Returns the last-updated date associated with the claim's image-alert flag for the given legal case.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `aLegalCaseNum` | IN | NUMBER | — | Legal case sequence number. |

### UPDATE_LEGALCASE_REQUEST

| Field | Value |
| --- | --- |
| **Returns** | none (procedure) |
| **Called by** | No confirmed .NET call site* |

**Description:** Updates the request-related fields of a legal case (issue type, WCB-requested flag, stress flag, requested-by, request/received/postmark dates, attorney firm/provider IDs, reconsideration letter flag, LCE-excused flag, OWCP flag). Calculates `OAR_DUE_DATE` as the postmark date plus 10 days, but only when the case has no existing reconsideration-flagged request issue (`v_recon_cnt = 0`); sets `CASE_WEIGHT_VALUE` to 3 when the stress-claim flag is `'Y'`, otherwise 1 ([BR-PKG1-007](../business-rules.md#br-pkg1-007-oar-due-date-and-case-weight-calculation)).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `aLegalCaseSeq` | IN | NUMBER | — | Legal case sequence number. |
| `aIssueType` | IN | `LEGAL_CASE.REQUEST_TYPE_CODE%TYPE` | — | Issue/request type code. |
| `aWCBRequested` | IN | `LEGAL_CASE.request_new_wcb_num_flag%TYPE` | — | Flag requesting a new WCB number. |
| `aStress` | IN | `LEGAL_CASE.stress_claim_flag%TYPE` | — | Stress-claim flag ([BR-PKG1-007](../business-rules.md#br-pkg1-007-oar-due-date-and-case-weight-calculation)). |
| `aRequestedBy` | IN | `LEGAL_CASE.REQUESTED_BY_CODE%TYPE` | — | Requested-by code. |
| `aRequestDate` | IN | VARCHAR2 | — | Request date (string). |
| `aReceivedDate` | IN | VARCHAR2 | — | Received date (string). |
| `aPostmarkDate` | IN | VARCHAR2 | — | Postmark date (string), basis of OAR due date ([BR-PKG1-007](../business-rules.md#br-pkg1-007-oar-due-date-and-case-weight-calculation)). |
| `aAttyFirmProvID` | IN | NUMBER | — | Attorney firm provider location ID. |
| `aReconLetter` | IN | VARCHAR2 | — | Reconsideration letter flag. |
| `aAttyProvID` | IN | NUMBER | — | Attorney provider location ID. |
| `aLCEExcused` | IN | VARCHAR2 | — | LCE excused flag ([BR-PKG1-008](../business-rules.md#br-pkg1-008-lce-excused-upsert-logic)). |
| `aOWCP` | IN | VARCHAR2 | — | OWCP flag. |
| `aUserID` | IN | VARCHAR2 | — | Audit user. |

### update_lce_Excused

| Field | Value |
| --- | --- |
| **Returns** | none (procedure) |
| **Called by** | No confirmed .NET call site* |

**Description:** Synchronizes the LCE excused flag for a case: if an LCE row already exists and its excused flag differs from the new value, updates it; if no LCE row exists and the new value is `'Y'`, inserts a new LCE row ([BR-PKG1-008](../business-rules.md#br-pkg1-008-lce-excused-upsert-logic)).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `aLegalCaseSeq` | IN | NUMBER | — | Legal case sequence number. |
| `aLCEExcused` | IN | VARCHAR2 | — | New excused flag value. |
| `aUserID` | IN | VARCHAR2 | — | Audit user. |

### setFileCreateDate

| Field | Value |
| --- | --- |
| **Returns** | none (procedure) |
| **Called by** | `clsCreateFile` |

**Description:** Sets `LEGAL_FILE_CREATE_DATE` on one or more legal cases identified by a delimited list of sequence numbers.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `seqNums` | IN | VARCHAR2 | — | Delimited list of legal case sequence numbers. |
| `inDate` | IN | DATE | — | File-create date to set. |
| `userid` | IN | VARCHAR2 | — | Audit user. |

### mailDateList

| Field | Value |
| --- | --- |
| **Returns** | `recordset` (REF CURSOR) |
| **Called by** | `clsMailDate` |

**Description:** Returns the list of legal cases eligible for mailing based on `inType` (OAR or reconsideration). A case is eligible for OAR mailing when its `mail_oar_flag = 'Y'`, it is not closed, `OAR_DUE_DATE IS NOT NULL`, and `OAR_MAILED_DATE IS NULL`; a case is eligible for reconsideration-packet follow-up when `RECON_LETTER_MAILED_DATE IS NOT NULL` and `RECON_PACKET_RECEIVED_DATE IS NULL` ([BR-PKG1-009](../business-rules.md#br-pkg1-009-oar-and-reconsideration-mail-eligibility)).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `recordset` | OUT | REF CURSOR | — | Eligible case rows. |
| `inType` | IN | VARCHAR2 | — | Mail list type (OAR or reconsideration). |

### setMailDate

| Field | Value |
| --- | --- |
| **Returns** | none (procedure) |
| **Called by** | `clsMailDate` |

**Description:** Sets the mail date (OAR mailed date or reconsideration letter mailed date, per `inType`) for one or more legal cases identified by a delimited list of sequence numbers.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `seqNums` | IN | VARCHAR2 | — | Delimited list of legal case sequence numbers. |
| `inDate` | IN | DATE | — | Mail date to set. |
| `inType` | IN | VARCHAR2 | — | Mail type (OAR or reconsideration). |
| `userid` | IN | VARCHAR2 | — | Audit user. |

### setNoMailDate

| Field | Value |
| --- | --- |
| **Returns** | NUMBER (status indicator) |
| **Called by** | No confirmed .NET call site* |

**Description:** Marks a case as not requiring a mail date. For a consolidated secondary case, the mail date (OAR mailed date or recon packet received date, per `inType`) is inherited from the case's consolidation primary rather than being set independently ([BR-PKG1-010](../business-rules.md#br-pkg1-010-consolidated-secondary-cases-inherit-mail-date-from-primary)).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `InseqNum` | IN | NUMBER | — | Legal case sequence number. |
| `inDate` | IN | DATE | — | Date to record. |
| `inType` | IN | VARCHAR2 | — | Mail type (OAR or reconsideration). |
| `userid` | IN | VARCHAR2 | — | Audit user. |

### setMailOARFlag

| Field | Value |
| --- | --- |
| **Returns** | NUMBER (status indicator) |
| **Called by** | `clsMailDate` |

**Description:** Sets `LEGAL_CASE.MAIL_OAR_FLAG = 'Y'` for a legal case, marking it eligible for OAR mail-date processing by `mailDateList` ([BR-PKG1-009](../business-rules.md#br-pkg1-009-oar-and-reconsideration-mail-eligibility)).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `InseqNum` | IN | NUMBER | — | Legal case sequence number. |
| `userid` | IN | VARCHAR2 | — | Audit user. |

### insertAppeal

| Field | Value |
| --- | --- |
| **Returns** | NUMBER (new `appeal_seq_num` / status indicator) |
| **Called by** | `clsAppeal` |

**Description:** Inserts a new `APPEAL` row (review level, due/filed dates, petitioner/respondent code, approved flag, attorney, brief 1/2/3 due/filed dates, appeal number, linked schedule). Wraps the insert in a `WHEN OTHERS` handler that re-raises via `RAISE_APPLICATION_ERROR(-20004, <message>)` on failure.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `AppealID` | IN | NUMBER | — | New appeal sequence number. |
| `LegalCaseID` | IN | NUMBER | — | Legal case sequence number. |
| `reviewLevel` | IN | VARCHAR2 | — | Appeal review level code. |
| `dueDate` | IN | DATE | — | Appeal due date. |
| `filedDate` | IN | DATE | — | Appeal filed date. |
| `PetitionerRespondentCode` | IN | VARCHAR2 | — | Petitioner/respondent code. |
| `approvedFlag` | IN | VARCHAR2 | — | Approved flag. |
| `attyEmplid` | IN | VARCHAR2 | — | Assigned attorney employee ID. |
| `brief1DueDate` | IN | DATE | — | Brief 1 due date. |
| `brief1FiledDate` | IN | DATE | — | Brief 1 filed date. |
| `brief2DueDate` | IN | DATE | — | Brief 2 due date. |
| `brief2FiledDate` | IN | DATE | — | Brief 2 filed date. |
| `brief3DueDate` | IN | DATE | — | Brief 3 due date. |
| `brief3FiledDate` | IN | DATE | — | Brief 3 filed date. |
| `appealNum` | IN | VARCHAR2 | — | External appeal number. |
| `ScheduleID` | IN | NUMBER | — | Linked schedule sequence number. |
| `createDate` | IN | DATE | — | Audit creation timestamp. |
| `createUser` | IN | VARCHAR2 | — | Audit creation user. |

### updateAppeal

| Field | Value |
| --- | --- |
| **Returns** | NUMBER (status indicator) |
| **Called by** | `clsAppeal` |

**Description:** Updates an existing `APPEAL` row with the same attribute set as `insertAppeal`, replacing `createDate`/`createUser` with `updateDate`/`updateUser`.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `AppealID` | IN | NUMBER | — | Appeal row to update. |
| `LegalCaseID` through `ScheduleID` | IN | (see `insertAppeal`) | — | Same attribute set as `insertAppeal`. |
| `updateDate` | IN | DATE | — | Audit update timestamp. |
| `updateUser` | IN | VARCHAR2 | — | Audit update user. |

### getAppeal

| Field | Value |
| --- | --- |
| **Returns** | `Recordset` (REF CURSOR) |
| **Called by** | `clsAppeal`, `clsAppealExtension` |

**Description:** Returns all `APPEAL` rows for a legal case.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `LegalCaseID` | IN | NUMBER | — | Legal case sequence number. |
| `Recordset` | OUT | REF CURSOR | — | Appeal row(s). |

### getAppealExtension

| Field | Value |
| --- | --- |
| **Returns** | `Recordset` (REF CURSOR) |
| **Called by** | No confirmed .NET call site* |

**Description:** Returns all `APPEAL_EXTENSION` rows for a given appeal.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `appealSeqNum` | IN | NUMBER | — | Appeal sequence number. |
| `Recordset` | OUT | REF CURSOR | — | Appeal extension row(s). |

### updateAppealExtension

| Field | Value |
| --- | --- |
| **Returns** | NUMBER (status indicator) |
| **Called by** | `clsAppealExtension` |

**Description:** Updates an existing `APPEAL_EXTENSION` row's request date and days requested.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `appealExtSeqNum` | IN | NUMBER | — | Appeal extension row to update. |
| `appealSeqNum` | IN | NUMBER | — | Owning appeal sequence number. |
| `requestDate` | IN | DATE | — | Extension request date. |
| `daysRequested` | IN | NUMBER | — | Number of days requested. |

### insertAppealExtension

| Field | Value |
| --- | --- |
| **Returns** | NUMBER (status indicator) |
| **Called by** | `clsAppealExtension` |

**Description:** Inserts a new `APPEAL_EXTENSION` row for an appeal.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `appealExtSeqNum` | IN | NUMBER | — | New appeal extension sequence number. |
| `appealSeqNum` | IN | NUMBER | — | Owning appeal sequence number. |
| `requestDate` | IN | DATE | — | Extension request date. |
| `daysRequested` | IN | NUMBER | — | Number of days requested. |

### deleteappeal

| Field | Value |
| --- | --- |
| **Returns** | NUMBER (status indicator) |
| **Called by** | `clsAppeal` |

**Description:** Deletes an `APPEAL` row and cascades cleanup: deletes the appeal's linked schedule (if any) and recalculates the case's hearing sequence number; conditionally calls `popcasestatus` to revert the case status when the deleted appeal's review level changed the case's status history, and/or `popfilelocation` to revert the file location when the assigned attorney/location code on the deleted appeal matches the current file location (including the special-case `APEAL` location code) ([BR-PKG1-004](../business-rules.md#br-pkg1-004-appeal-deletion-cascades-status-and-file-location-reversion)).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `inlegalcaseseqnum` | IN | NUMBER | — | Legal case sequence number. |
| `inappealseqnum` | IN | NUMBER | — | Appeal sequence number to delete. |

### popcasestatus

| Field | Value |
| --- | --- |
| **Returns** | none (procedure) |
| **Called by** | No confirmed .NET call site* |

**Description:** Reverts a legal case's status to its prior value from case-status history, invoked by `deleteappeal` when deleting an appeal changes the case's status lineage ([BR-PKG1-004](../business-rules.md#br-pkg1-004-appeal-deletion-cascades-status-and-file-location-reversion)).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `inlegalcaseseqnum` | IN | `LEGAL_CASE.legal_case_seq_num%TYPE` | — | Legal case sequence number. |

### popfilelocation

| Field | Value |
| --- | --- |
| **Returns** | none (procedure) |
| **Called by** | No confirmed .NET call site* |

**Description:** Reverts a legal case's file location by deleting the most recent `LEGAL_FILE_LOCATION` row (file location is versioned, ordered by `move_date` descending, with the most recent row authoritative), invoked by `deleteappeal` ([BR-PKG1-004](../business-rules.md#br-pkg1-004-appeal-deletion-cascades-status-and-file-location-reversion), [BR-PKG1-011](../business-rules.md#br-pkg1-011-file-location-is-versioned-by-move-date)).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `inlegalcaseseqnum` | IN | `LEGAL_CASE.legal_case_seq_num%TYPE` | — | Legal case sequence number. |

### getBoardLog

| Field | Value |
| --- | --- |
| **Returns** | `Recordset` (REF CURSOR) |
| **Called by** | `clsBoardLog` |

**Description:** Returns board-log reference data (e.g. review-level/appeal board lookup rows) used to populate the LegalNet appeal UI.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `Recordset` | OUT | REF CURSOR | — | Board log rows. |

### updateAppealAtty

| Field | Value |
| --- | --- |
| **Returns** | none (procedure) |
| **Called by** | No confirmed .NET call site* |

**Description:** Updates the attorney assigned to a specific appeal.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `appealSeqNum` | IN | NUMBER | — | Appeal sequence number. |
| `attyEmplid` | IN | VARCHAR2 | — | Attorney employee ID to assign. |

### getAcknowledgeList

| Field | Value |
| --- | --- |
| **Returns** | `recordset` (REF CURSOR) |
| **Called by** | `clsAcknowledgeList` |

**Description:** Returns the list of legal cases pending acknowledgement-letter processing for a given type.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `recordset` | OUT | REF CURSOR | — | Pending acknowledgement rows. |
| `inType` | IN | VARCHAR2 | — | Acknowledgement list type. |

### updateAcknowledged

| Field | Value |
| --- | --- |
| **Returns** | NUMBER (status indicator) |
| **Called by** | `clsAcknowledgeList` |

**Description:** Records that a legal case's acknowledgement letter of the given type was sent on the given date.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `inType` | IN | VARCHAR2 | — | Acknowledgement type. |
| `inNum` | IN | VARCHAR2 | — | Case identifier. |
| `inDate` | IN | DATE | — | Date acknowledged. |
| `CaseId` | IN | NUMBER | — | Legal case sequence number. |

### updateDEFfields

| Field | Value |
| --- | --- |
| **Returns** | none (procedure) |
| **Called by** | No confirmed .NET call site* |

**Description:** Updates a legal case's "Denial/Extension/Filing" (DEF) tracking fields — a case identifier, a date, and a flag.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `aLegalCaseNum` | IN | NUMBER | — | Legal case sequence number. |
| `inNum` | IN | VARCHAR2 | — | DEF-related case identifier. |
| `inDate` | IN | DATE | — | DEF-related date. |
| `inFlag` | IN | VARCHAR2 | — | DEF-related flag. |
| `inUser` | IN | VARCHAR2 | — | Audit user. |

### getFileLocation

| Field | Value |
| --- | --- |
| **Returns** | `Recordset` (REF CURSOR) |
| **Called by** | `clsLegalFileLocation` |

**Description:** Returns the `LEGAL_FILE_LOCATION` history for a legal case, most recent first ([BR-PKG1-011](../business-rules.md#br-pkg1-011-file-location-is-versioned-by-move-date)).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `LegalCaseID` | IN | NUMBER | — | Legal case sequence number. |
| `Recordset` | OUT | REF CURSOR | — | File location row(s). |

### updateFileLocation

| Field | Value |
| --- | --- |
| **Returns** | NUMBER (status indicator) |
| **Called by** | `clsLegalFileLocation` |

**Description:** Updates an existing `LEGAL_FILE_LOCATION` row's location person, location code, and move date.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `LegalFileLocationID` | IN | NUMBER | — | File location row to update. |
| `LegalCaseID` | IN | NUMBER | — | Legal case sequence number. |
| `LocationPersonEmplid` | IN | VARCHAR2 | — | Employee holding the file. |
| `LegalFileLocationCode` | IN | VARCHAR2 | — | Location code. |
| `moveDate` | IN | DATE | — | Date of the move. |

### insertFileLocation

| Field | Value |
| --- | --- |
| **Returns** | NUMBER (status indicator) |
| **Called by** | `clsLegalFileLocation` |

**Description:** Inserts a new `LEGAL_FILE_LOCATION` row, versioning the file's location history for the case ([BR-PKG1-011](../business-rules.md#br-pkg1-011-file-location-is-versioned-by-move-date)).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `LegalFileLocationID` | IN | NUMBER | — | New file location sequence number. |
| `LegalCaseID` | IN | NUMBER | — | Legal case sequence number. |
| `LocationPersonEmplid` | IN | VARCHAR2 | — | Employee holding the file. |
| `LegalFileLocationCode` | IN | VARCHAR2 | — | Location code. |
| `moveDate` | IN | DATE | — | Date of the move. |
| `createDate` | IN | DATE | — | Audit creation timestamp. |
| `createUser` | IN | VARCHAR2 | — | Audit creation user. |

### getAttyProfile

| Field | Value |
| --- | --- |
| **Returns** | `Recordset` (REF CURSOR) |
| **Called by** | `clsAttorneyProfile` |

**Description:** Returns all `ATTORNEY_PROFILE` rows (claim-type flags, load percent, hearing location assignments, claim team, attorney type, secretary/manager, OSB number).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `Recordset` | OUT | REF CURSOR | — | Attorney profile row(s). |

### updateAttyProfile

| Field | Value |
| --- | --- |
| **Returns** | NUMBER (status indicator) |
| **Called by** | `clsAttorneyProfile` |

**Description:** Updates an existing `ATTORNEY_PROFILE` row's effective/expiration dates, claim-type eligibility flags, load percent, hearing location assignments, claim team, attorney type, secretary/manager, and OSB number.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `profileEmplid` | IN | VARCHAR2 | — | Attorney employee ID. |
| `EffDate` | IN | DATE | — | Profile effective date. |
| `ExpDate` | IN | DATE | — | Profile expiration date. |
| `CcuClaimsFlag` | IN | VARCHAR2 | — | Eligible for CCU claims flag. |
| `PtdClaimsFlag` | IN | VARCHAR2 | — | Eligible for PTD claims flag. |
| `PpdClaimsFlag` | IN | VARCHAR2 | — | Eligible for PPD claims flag. |
| `ConfidentialClaimsFlag` | IN | VARCHAR2 | — | Eligible for confidential claims flag. |
| `HealthIssueClaimsFlag` | IN | VARCHAR2 | — | Eligible for health-issue claims flag. |
| `DcbsClaimsFlag` | IN | VARCHAR2 | — | Eligible for DCBS claims flag. |
| `FederalClaimsFlag` | IN | VARCHAR2 | — | Eligible for federal claims flag. |
| `LoadPercent` | IN | NUMBER | — | Caseload percentage. |
| `PrimaryHearLocCode` | IN | VARCHAR2 | — | Primary hearing location code. |
| `SecondaryHearLocCode` | IN | VARCHAR2 | — | Secondary hearing location code. |
| `ClaimTeamCode` | IN | VARCHAR2 | — | Claim team code. |
| `AttorneyTypeCode` | IN | VARCHAR2 | — | Attorney type code. |
| `SecretaryEmplid` | IN | VARCHAR2 | — | Secretary's employee ID. |
| `ManagerEmplid` | IN | VARCHAR2 | — | Manager's employee ID. |
| `TertiaryHearLocCode` | IN | VARCHAR2 | — | Tertiary hearing location code. |
| `osbNumber` | IN | VARCHAR2 | — | Oregon State Bar number. |

### insertAttyProfile

| Field | Value |
| --- | --- |
| **Returns** | NUMBER (status indicator) |
| **Called by** | `clsAttorneyProfile` |

**Description:** Inserts a new `ATTORNEY_PROFILE` row with the same attribute set as `updateAttyProfile`.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `profileEmplid` through `osbNumber` | IN | (see `updateAttyProfile`) | — | Same attribute set as `updateAttyProfile`. |

### getAttySchedule

| Field | Value |
| --- | --- |
| **Returns** | `Recordset` (`attySched_cursor` REF CURSOR: schedule seq num, emplid, eff/exp date, type code, duration code) |
| **Called by** | No confirmed .NET call site* |

**Description:** Returns all attorney out-of-office/schedule-block rows (`ATTORNEY_SCHEDULE`).*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `Recordset` | OUT | `attySched_cursor` | — | Attorney schedule row(s). |

### updateAttySchedule

| Field | Value |
| --- | --- |
| **Returns** | none (procedure) |
| **Called by** | No confirmed .NET call site* |

**Description:** Updates an existing `ATTORNEY_SCHEDULE` row's effective/expiration dates, type code, and duration code.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `attySchedSeqNum` | IN | NUMBER | — | Attorney schedule row to update. |
| `scheduleEmplid` | IN | VARCHAR2 | — | Attorney employee ID. |
| `effDate` | IN | DATE | — | Effective date. |
| `expDate` | IN | DATE | — | Expiration date. |
| `typeCode` | IN | VARCHAR2 | — | Attorney schedule type code. |
| `durationCode` | IN | VARCHAR2 | — | Duration code. |

### insertAttySchedule

| Field | Value |
| --- | --- |
| **Returns** | none (procedure) |
| **Called by** | No confirmed .NET call site* |

**Description:** Inserts a new `ATTORNEY_SCHEDULE` row with the same attribute set as `updateAttySchedule`.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `attySchedSeqNum` | IN | NUMBER | — | New attorney schedule sequence number. |
| `scheduleEmplid` through `durationCode` | IN | (see `updateAttySchedule`) | — | Same attribute set as `updateAttySchedule`. |

### deleteAttySchedule

| Field | Value |
| --- | --- |
| **Returns** | none (procedure) |
| **Called by** | No confirmed .NET call site* |

**Description:** Deletes an `ATTORNEY_SCHEDULE` row.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `attySchedSeqNum` | IN | NUMBER | — | Attorney schedule row to delete. |

### getConsolidatedCases

| Field | Value |
| --- | --- |
| **Returns** | `recordset` (REF CURSOR) |
| **Called by** | `clsConsolidations` |

**Description:** Returns the set of consolidated cases for a comma-delimited list of claim IDs. A case is displayed if it is not closed and is not itself a consolidated secondary, OR if it is a consolidated secondary regardless of closed status — so secondary/consolidated cases remain visible even after closure ([BR-PKG1-012](../business-rules.md#br-pkg1-012-consolidated-secondary-cases-remain-visible-when-closed)).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `recordset` | OUT | REF CURSOR | — | Consolidated case rows. |
| `claimIdsCommaDelimited` | IN | VARCHAR2 | — | Comma-delimited list of claim IDs. |

### UpdateConsolidatedCase

| Field | Value |
| --- | --- |
| **Returns** | none (procedure) |
| **Called by** | `clsConsolidations` |

**Description:** Updates a `CONSOLIDATED_CASE` link between a primary and secondary case. Reassigning a secondary to a new primary migrates the most recent hearing schedule to the new primary; deleting the link (`aConsolFlag = 'N'`) recalculates and resets `MOST_RECENT_HEARING_SEQ_NUM` on the now-independent secondary case based on its own most recent HEAR schedule ([BR-PKG1-013](../business-rules.md#br-pkg1-013-consolidation-link-changes-migrate-or-reset-hearing-sequence)).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `aConsolidatedSeq` | IN | `CONSOLIDATED_CASE.consolidated_case_seq_num%TYPE` | — | Consolidation link sequence number. |
| `aPrimarySeq` | IN | `CONSOLIDATED_CASE.primary_case_seq_num%TYPE` | — | Primary case sequence number. |
| `aSecondarySeq` | IN | `CONSOLIDATED_CASE.secondary_case_seq_num%TYPE` | — | Secondary case sequence number. |
| `aConsolFlag` | IN | VARCHAR2 | — | `'Y'` to establish/reassign the link, `'N'` to remove it ([BR-PKG1-013](../business-rules.md#br-pkg1-013-consolidation-link-changes-migrate-or-reset-hearing-sequence)). |
| `UpdateDate` | IN | DATE | — | Audit update timestamp. |
| `UpdateUser` | IN | VARCHAR2 | — | Audit update user. |

### getAttorneyNames

| Field | Value |
| --- | --- |
| **Returns** | `recordset` (`attorney_cursor` REF CURSOR: attorney name, legal person seq num) |
| **Called by** | No confirmed .NET call site* |

**Description:** Returns the list of attorneys (legal persons) for populating attorney-selection lookups.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `recordset` | OUT | `attorney_cursor` | — | Attorney name/ID rows. |

### insertLetterRequest

| Field | Value |
| --- | --- |
| **Returns** | `requestNum` (IN OUT NUMBER) |
| **Called by** | No confirmed .NET call site* |

**Description:** Records a request for a legal letter (e.g. acknowledgement or DEF letter) to be generated for a case, returning/assigning the request number.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `letterTypeCode` | IN | VARCHAR2 | — | Letter type code. |
| `aLegalCaseNum` | IN | NUMBER | — | Legal case sequence number. |
| `holdDate` | IN | DATE | — | Hold/scheduled date for the letter. |
| `aClaimId` | IN | NUMBER | — | Associated claim ID. |
| `createUser` | IN | VARCHAR2 | — | Audit creation user. |
| `requestNum` | IN OUT | NUMBER | — | Letter request number (assigned if not supplied). |

### getHearingInfo

| Field | Value |
| --- | --- |
| **Returns** | `recordset` (`HearingCursor` REF CURSOR: schedule date/time, duration, keep-attorney flag, assigned/reminder date, schedule text, attorney/secretary mail IDs and names, ALJ name, hearing location detail, hearing status, all-day flag) |
| **Called by** | `clsHearingLocationInfo` |

**Description:** Returns full hearing detail for a specific schedule row, joining attorney, secretary, ALJ, and hearing-location reference data.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_HearingSeq` | IN | `SCHEDULE.schedule_seq_num%TYPE` | — | Schedule sequence number. |
| `recordset` | OUT | `HearingCursor` | — | Hearing detail row. |

### getEmployerAddr

| Field | Value |
| --- | --- |
| **Returns** | `recordset` (`EmployerCursor` REF CURSOR: policy name, address lines, city/state/zip, mail-address flag, phone, location seq num) |
| **Called by** | No confirmed .NET call site* |

**Description:** Returns the employer's mailing address for a policy number.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_Policy` | IN | `POLICY.policy_num%TYPE` | — | Policy number. |
| `recordset` | OUT | `EmployerCursor` | — | Employer address row(s). |

### getOtherFirmList

| Field | Value |
| --- | --- |
| **Returns** | `recordset` (`OtherFirmListCursor` REF CURSOR: type code, legal case employer seq num, other firm seq num, discovery flags, firm name/type, address, phone/fax) |
| **Called by** | No confirmed .NET call site* |

**Description:** Returns the list of other-firm (employer/attorney) associations recorded for a legal case.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `aLegalCaseNum` | IN | NUMBER | — | Legal case sequence number. |
| `recordset` | OUT | `OtherFirmListCursor` | — | Other-firm association row(s). |

### GetEmployeebyMailid

| Field | Value |
| --- | --- |
| **Returns** | `recordset` (`EmployeeCursor` REF CURSOR: emplid, name fields, email, status, phone, location, dept, reports-to, user ID) |
| **Called by** | No confirmed .NET call site* |

**Description:** Looks up ADP employee detail by mail/user ID.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_MailId` | IN | VARCHAR | — | Mail/user ID to look up. |
| `recordset` | OUT | `EmployeeCursor` | — | Employee detail row. |

### GetEmployeeName

| Field | Value |
| --- | --- |
| **Returns** | VARCHAR2 |
| **Called by** | No confirmed .NET call site* |

**Description:** Returns the display name of an ADP employee for a given mail/user ID.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_mailid` | IN | VARCHAR | — | Mail/user ID. |

### IsLetterRequested

| Field | Value |
| --- | --- |
| **Returns** | `out_requested` (OUT VARCHAR2) |
| **Called by** | No confirmed .NET call site* |

**Description:** Indicates whether a letter of the given type has already been requested for a legal case, to prevent duplicate letter requests.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `aLegalCaseNum` | IN | NUMBER | — | Legal case sequence number. |
| `aLetterType` | IN | VARCHAR2 | — | Letter type code. |
| `out_requested` | OUT | VARCHAR2 | — | `'Y'`/`'N'` indicating whether already requested. |

### GetCaseCost

| Field | Value |
| --- | --- |
| **Returns** | `recordset` (`CaseCostCursor` REF CURSOR) |
| **Called by** | No confirmed .NET call site* |

**Description:** Returns all `CASE_COST` rows (service dates, service code, amount paid/billed, hourly rate, hours billed, prepayment flag, authorized date, payee firm/person provider location, authorizer, cancelled-by code) for a legal case.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `aLegalCaseNum` | IN | NUMBER | — | Legal case sequence number. |
| `recordset` | OUT | `CaseCostCursor` | — | Case cost row(s). |

### case_cost_update

| Field | Value |
| --- | --- |
| **Returns** | none (procedure) |
| **Called by** | No confirmed .NET call site* |

**Description:** Updates an existing `CASE_COST` row's service dates, service code, amounts, prepayment/authorization info, and payee/authorizer identifiers.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `case_cost_seq_num_in` | IN | NUMBER | — | Case cost row to update. |
| `legal_case_seq_num_in` | IN | NUMBER | — | Legal case sequence number. |
| `service_from_date_in` | IN | DATE | — | Service period start date. |
| `service_to_date_in` | IN | DATE | — | Service period end date. |
| `service_code_in` | IN | VARCHAR2 | — | Service code. |
| `amount_paid_in` | IN | NUMBER | — | Amount paid. |
| `amount_billed_in` | IN | NUMBER | — | Amount billed. |
| `amount_per_hour_in` | IN | NUMBER | — | Hourly rate. |
| `hours_billed_in` | IN | NUMBER | — | Hours billed. |
| `prepayment_flag_in` | IN | VARCHAR2 | — | Prepayment flag. |
| `authorized_date_in` | IN | DATE | — | Authorization date. |
| `payee_firm_prov_loc_id_in` | IN | NUMBER | — | Payee firm provider location ID. |
| `payee_person_prov_loc_id_in` | IN | NUMBER | — | Payee person provider location ID. |
| `authorizer_emplid_in` | IN | VARCHAR2 | — | Authorizer employee ID. |
| `cancelled_by_code_in` | IN | VARCHAR2 | — | Cancelled-by code. |
| `update_date_in` | IN | DATE | — | Audit update timestamp. |
| `update_user_in` | IN | VARCHAR2 | — | Audit update user. |

### case_cost_insert

| Field | Value |
| --- | --- |
| **Returns** | none (procedure) |
| **Called by** | No confirmed .NET call site* |

**Description:** Inserts a new `CASE_COST` row with the same attribute set as `case_cost_update`, replacing `update_date_in`/`update_user_in` with `create_date_in`/`create_user_in`.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `case_cost_seq_num_in` through `cancelled_by_code_in` | IN | (see `case_cost_update`) | — | Same attribute set as `case_cost_update`. |
| `create_date_in` | IN | DATE | — | Audit creation timestamp. |
| `create_user_in` | IN | VARCHAR2 | — | Audit creation user. |

### case_cost_delete

| Field | Value |
| --- | --- |
| **Returns** | none (procedure) |
| **Called by** | No confirmed .NET call site* |

**Description:** Deletes a `CASE_COST` row.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `case_cost_seq_num_in` | IN | NUMBER | — | Case cost row to delete. |

### DeleteLegalCase

| Field | Value |
| --- | --- |
| **Returns** | NUMBER (status indicator) |
| **Called by** | `clsLegalCase` |

**Description:** Deletes a `LEGAL_CASE` row and its sequence number.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `LegalCaseSeqNum` | IN | `LEGAL_CASE.legal_case_Seq_num%TYPE` | — | Legal case sequence number to delete. |

### GetCaseNote

| Field | Value |
| --- | --- |
| **Returns** | `recordset` (REF CURSOR) |
| **Called by** | `clsCaseNote` |

**Description:** Returns all `CASE_NOTE` rows for a legal case.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `CaseId` | IN | NUMBER | — | Legal case sequence number. |
| `recordset` | OUT | REF CURSOR | — | Case note row(s). |

### case_note_insert

| Field | Value |
| --- | --- |
| **Returns** | NUMBER (status indicator) |
| **Called by** | `clsCaseNote` |

**Description:** Inserts a new `CASE_NOTE` row (category code and free text) for a legal case.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `noteId` | IN | NUMBER | — | New case note sequence number. |
| `caseId` | IN | NUMBER | — | Legal case sequence number. |
| `categoryCode` | IN | VARCHAR2 | — | Note category code. |
| `Notetext` | IN | VARCHAR2 | — | Note free text. |
| `createDate` | IN | DATE | — | Audit creation timestamp. |
| `createUser` | IN | VARCHAR2 | — | Audit creation user. |

### case_note_update

| Field | Value |
| --- | --- |
| **Returns** | NUMBER (status indicator) |
| **Called by** | `clsCaseNote` |

**Description:** Updates an existing `CASE_NOTE` row's category code and free text.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `noteId` | IN | NUMBER | — | Case note row to update. |
| `caseId` | IN | NUMBER | — | Legal case sequence number. |
| `categoryCode` | IN | VARCHAR2 | — | Note category code. |
| `NoteText` | IN | VARCHAR2 | — | Note free text. |
| `updateDate` | IN | DATE | — | Audit update timestamp. |
| `updateUser` | IN | VARCHAR2 | — | Audit update user. |

### case_note_delete

| Field | Value |
| --- | --- |
| **Returns** | NUMBER (status indicator) |
| **Called by** | `clsCaseNote` |

**Description:** Deletes a `CASE_NOTE` row.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `noteId` | IN | NUMBER | — | Case note row to delete. |

### GetSAIFAtty

| Field | Value |
| --- | --- |
| **Returns** | `recordset` (REF CURSOR) |
| **Called by** | attorney lookup (`clsLegalCase` area) |

**Description:** Returns the list of SAIF legal-staff attorneys for a legal case, used to populate attorney-assignment dropdowns.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `aLegalCaseNum` | IN | NUMBER | — | Legal case sequence number. |
| `recordset` | OUT | REF CURSOR | — | Attorney row(s). |

### GetCaseOutcome

| Field | Value |
| --- | --- |
| **Returns** | `recordset` (REF CURSOR) |
| **Called by** | `clsCaseOutcome` |

**Description:** Returns all `CASE_OUTCOME` rows (order type, signed/filed dates, assigned attorney, appeal link) for a legal case.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `LegalCaseID` | IN | NUMBER | — | Legal case sequence number. |
| `recordset` | OUT | REF CURSOR | — | Case outcome row(s). |

### GetOutcomeIssue

| Field | Value |
| --- | --- |
| **Returns** | `recordset` (`OutcomeIssueCursor` REF CURSOR: outcome issue seq num, case outcome seq num, issue code, win/loss flag, fee amount, penalty flag, primary-issue flag, audit fields, summary text, grounds/body-part/nature codes) |
| **Called by** | No confirmed .NET call site* |

**Description:** Returns all `OUTCOME_ISSUE` rows for a legal case's outcome(s).*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `aLegalCaseNum` | IN | NUMBER | — | Legal case sequence number. |
| `recordset` | OUT | `OutcomeIssueCursor` | — | Outcome issue row(s). |

### getAljList

| Field | Value |
| --- | --- |
| **Returns** | `recordset` (`ALJListCursor` REF CURSOR: legal person seq num, last name, legal person type code, code description, selected flag) |
| **Called by** | No confirmed .NET call site* |

**Description:** Returns the list of ALJs (Administrative Law Judges) available for a legal case's outcome, flagging the currently selected one.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `aLegalCaseNum` | IN | NUMBER | — | Legal case sequence number. |
| `recordset` | OUT | `ALJListCursor` | — | ALJ row(s). |

### GetOutcomeStaff

| Field | Value |
| --- | --- |
| **Returns** | `recordset` (`OutcomeStaffCursor` REF CURSOR: outcome staff seq num, case outcome seq num, outcome staff person seq num, last name) |
| **Called by** | No confirmed .NET call site* |

**Description:** Returns the staff persons associated with a legal case's outcome(s).*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `aLegalCaseNum` | IN | NUMBER | — | Legal case sequence number. |
| `recordset` | OUT | `OutcomeStaffCursor` | — | Outcome staff row(s). |

### updateCaseOutcome

| Field | Value |
| --- | --- |
| **Returns** | NUMBER (status indicator) |
| **Called by** | `clsCaseOutcome` |

**Description:** Updates an existing `CASE_OUTCOME` row's order type, signed/filed dates, assigned attorney, and appeal link. When `SetFinalOutcome`-eligible order types are recorded, the case outcome becomes the case's final outcome of record ([BR-PKG1-014](../business-rules.md#br-pkg1-014-final-outcome-recording-drives-case-closure-eligibility)).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `CaseOutcomeID` | IN | NUMBER | — | Case outcome row to update. |
| `LegalCaseID` | IN | NUMBER | — | Legal case sequence number. |
| `OrderTypeCode` | IN | VARCHAR2 | — | Order type code. |
| `OrderSignedDate` | IN | DATE | NULL | Order signed date. |
| `OrderFiledDate` | IN | DATE | NULL | Order filed date. |
| `UpdateUser` | IN | VARCHAR2 | NULL | Audit update user. |
| `AsgnAttyEmplid` | IN | VARCHAR2 | NULL | Assigned attorney employee ID. |
| `AppealId` | IN | NUMBER | NULL | Linked appeal sequence number. |

### insertCaseOutcome

| Field | Value |
| --- | --- |
| **Returns** | NUMBER (status indicator) |
| **Called by** | `clsCaseOutcome` |

**Description:** Inserts a new `CASE_OUTCOME` row with the same attribute set as `updateCaseOutcome`.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `CaseOutcomeID` through `AppealId` | IN | (see `updateCaseOutcome`) | — | Same attribute set as `updateCaseOutcome`. |

### updateOutcomeIssue

| Field | Value |
| --- | --- |
| **Returns** | none (procedure) |
| **Called by** | No confirmed .NET call site* |

**Description:** Upserts an `OUTCOME_ISSUE` row: updates the row if `outcome_issue_seq_num_in` already exists, otherwise inserts a new row ([BR-PKG1-015](../business-rules.md#br-pkg1-015-outcome-issue-and-outcome-staff-use-upsert-semantics)).*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `outcome_issue_seq_num_in` | IN | NUMBER | — | Outcome issue sequence number (upsert key). |
| `case_outcome_seq_num_in` | IN | NUMBER | — | Owning case outcome sequence number. |
| `outcome_issue_code_in` | IN | VARCHAR2 | — | Outcome issue code. |
| `win_loss_flag_in` | IN | VARCHAR2 | — | Win/loss flag. |
| `fee_amount_in` | IN | NUMBER | — | Fee amount. |
| `penalty_flag_in` | IN | VARCHAR2 | — | Penalty flag. |
| `primary_issue_flag_in` | IN | VARCHAR2 | — | Primary-issue flag. |
| `summary_text_in` | IN | VARCHAR2 | — | Summary text. |
| `outcome_grounds_code_in` | IN | VARCHAR2 | — | Outcome grounds code. |
| `body_part_code_in` | IN | VARCHAR2 | — | Legal body part code. |
| `nature_code_in` | IN | VARCHAR2 | — | Nature code. |
| `userid_in` | IN | VARCHAR2 | — | Audit user. |

### updateOutcomeStaff

| Field | Value |
| --- | --- |
| **Returns** | none (procedure) |
| **Called by** | No confirmed .NET call site* |

**Description:** Upserts an `OUTCOME_STAFF` row: updates the row if `outcome_staff_seq_num_in` already exists, otherwise inserts a new row ([BR-PKG1-015](../business-rules.md#br-pkg1-015-outcome-issue-and-outcome-staff-use-upsert-semantics)).*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `outcome_staff_seq_num_in` | IN | NUMBER | — | Outcome staff sequence number (upsert key). |
| `case_outcome_seq_num_in` | IN | NUMBER | — | Owning case outcome sequence number. |
| `staff_person_seq_num_in` | IN | NUMBER | — | Staff legal-person sequence number. |

### DeleteCaseOutcome

| Field | Value |
| --- | --- |
| **Returns** | NUMBER (status indicator) |
| **Called by** | `clsCaseOutcome` |

**Description:** Deletes a `CASE_OUTCOME` row.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `case_outcome_seq_num_in` | IN | NUMBER | — | Case outcome row to delete. |

### DeleteOutcomeIssue

| Field | Value |
| --- | --- |
| **Returns** | none (procedure) |
| **Called by** | No confirmed .NET call site* |

**Description:** Deletes an `OUTCOME_ISSUE` row.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `outcome_issue_seq_num_in` | IN | NUMBER | — | Outcome issue row to delete. |

### DeleteOutcomeStaff

| Field | Value |
| --- | --- |
| **Returns** | none (procedure) |
| **Called by** | No confirmed .NET call site* |

**Description:** Deletes an `OUTCOME_STAFF` row.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `outcome_staff_seq_num_in` | IN | NUMBER | — | Outcome staff row to delete. |

### SetFinalOutcome

| Field | Value |
| --- | --- |
| **Returns** | none (procedure) |
| **Called by** | No confirmed .NET call site* |

**Description:** Marks the final outcome of record for a legal case, driving case-closure eligibility ([BR-PKG1-014](../business-rules.md#br-pkg1-014-final-outcome-recording-drives-case-closure-eligibility)).*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `legal_case_seq_num_in` | IN | NUMBER | — | Legal case sequence number. |

### GetLegalCaseEmployer

| Field | Value |
| --- | --- |
| **Returns** | `recordset` (`LegalCaseEmployerCursor` REF CURSOR: legal case employer seq num, legal case seq num, employer/attorney other-firm seq nums, attorney legal person seq num, employer/attorney discovery flags) |
| **Called by** | No confirmed .NET call site* |

**Description:** Returns the employer and opposing-attorney association row(s) for a legal case, including discovery-request flags.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `aLegalCaseNum` | IN | NUMBER | — | Legal case sequence number. |
| `recordset` | OUT | `LegalCaseEmployerCursor` | — | Legal case employer row(s). |

### legal_case_employer_insert

| Field | Value |
| --- | --- |
| **Returns** | none (procedure) |
| **Called by** | No confirmed .NET call site* |

**Description:** Inserts a new `LEGAL_CASE_EMPLOYER` row associating an employer and/or opposing attorney other-firm with a legal case, including discovery-request flags.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `legal_case_employer_seq_num_in` | IN | NUMBER | — | New legal case employer sequence number. |
| `legal_case_seq_num_in` | IN | NUMBER | — | Legal case sequence number. |
| `other_firm_seq_num_in` | IN | NUMBER | — | Employer other-firm sequence number. |
| `discovery_flag_in` | IN | VARCHAR2 | — | Employer discovery flag. |
| `attorney_discovery_flag_in` | IN | VARCHAR2 | — | Attorney discovery flag. |
| `atty_other_firm_seq_num_in` | IN | NUMBER | — | Attorney other-firm sequence number. |
| `atty_legal_person_seq_num_in` | IN | NUMBER | — | Attorney legal-person sequence number. |

### legal_case_employer_update

| Field | Value |
| --- | --- |
| **Returns** | none (procedure) |
| **Called by** | No confirmed .NET call site* |

**Description:** Updates an existing `LEGAL_CASE_EMPLOYER` row with the same attribute set as `legal_case_employer_insert`.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `legal_case_employer_seq_num_in` through `atty_legal_person_seq_num_in` | IN | (see `legal_case_employer_insert`) | — | Same attribute set as `legal_case_employer_insert`. |

### GetWorkerAtty

| Field | Value |
| --- | --- |
| **Returns** | `recordset` (REF CURSOR) |
| **Called by** | `clsAttorneyWorkerAssignment` |

**Description:** Returns the attorney(s) assigned to a worker for a given firm/attorney location, used for worker-attorney assignment lookups.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `recordset` | OUT | REF CURSOR | — | Assigned attorney row(s). |
| `inFirmLocID` | IN | NUMBER | — | Firm provider location ID. |
| `inAttyLocID` | IN | NUMBER | — | Attorney provider location ID. |

### GetAttyLeaveReport

| Field | Value |
| --- | --- |
| **Returns** | `recordset` (`AttyLeaveCursor` REF CURSOR: schedule emplid, attorney name, vacation days, medical-leave days) |
| **Called by** | No confirmed .NET call site* |

**Description:** Returns a leave-usage summary (vacation and medical days) per attorney across a date range, aggregating `ATTORNEY_SCHEDULE` rows.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `inStartDate` | IN | DATE | — | Report period start date. |
| `inStopDate` | IN | DATE | — | Report period end date. |
| `recordset` | OUT | `AttyLeaveCursor` | — | Attorney leave summary row(s). |

### alertlegalcase

| Field | Value |
| --- | --- |
| **Returns** | `OutAlertFlag` (OUT VARCHAR2) |
| **Called by** | No confirmed .NET call site* |

**Description:** Indicates whether a claim has an active legal-case alert (e.g. open legal case requiring attention), for surfacing an alert indicator on the claim.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `inClaimId` | IN | NUMBER | — | Claim ID to check. |
| `OutAlertFlag` | OUT | VARCHAR2 | — | `'Y'`/`'N'` alert indicator. |

### SelectImageAlertsHist

| Field | Value |
| --- | --- |
| **Returns** | `recordset` (REF CURSOR) |
| **Called by** | `clsImageAlerts` |

**Description:** Returns the image-alert history for a legal case.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `inCaseSeqNum` | IN | NUMBER | — | Legal case sequence number. |
| `recordset` | OUT | REF CURSOR | — | Image alert history row(s). |

### SelectLegalEmployees

| Field | Value |
| --- | --- |
| **Returns** | `recordset` (REF CURSOR) |
| **Called by** | `mdlCodeTables.clsLegalStaffList` |

**Description:** Returns the list of legal-department employees for staff-selection lookups.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `recordset` | OUT | REF CURSOR | — | Legal employee row(s). |

### getPolicyAddresses

| Field | Value |
| --- | --- |
| **Returns** | `recordset` (REF CURSOR) |
| **Called by** | No confirmed .NET call site* |

**Description:** Returns mailing address(es) associated with a policy number.*

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `recordset` | OUT | REF CURSOR | — | Policy address row(s). |
| `inPolicyNum` | IN | NUMBER | — | Policy number to look up. |

## Business Rules

| Rule ID | Rule | Condition | Action |
| --- | --- | --- | --- |
| [BR-PKG1-001](../business-rules.md#br-pkg1-001-case-search-excludes-closed-cases-by-default) | Case search excludes closed cases by default | `Casesearch` called with `includeClosed <> 'Y'` | Excludes rows where `CASE_STATUS_CODE = 'CLOSE'` from the result set. |
| [BR-PKG1-002](../business-rules.md#br-pkg1-002-case-status-changes-cascade-to-consolidated-secondary-cases) | Case status changes cascade to consolidated secondary cases | `updateCaseStatus` changes the status of a case that is a consolidation primary | Applies the same status update to all consolidated secondary cases linked to the primary. |
| [BR-PKG1-003](../business-rules.md#br-pkg1-003-hearing-sequence-number-fallback-order) | Hearing sequence number fallback order | `updateHearingSeqNum` recalculates `MOST_RECENT_HEARING_SEQ_NUM` | Uses the consolidation primary's most recent `HEAR` schedule if the case is a consolidated secondary; otherwise falls back to the case's own most recent `HEAR` schedule, then its own most recent `CCALL`/`INCON`/`MED` schedule. |
| [BR-PKG1-004](../business-rules.md#br-pkg1-004-appeal-deletion-cascades-status-and-file-location-reversion) | Appeal deletion cascades status and file location reversion | `deleteappeal` removes an `APPEAL` row | Deletes the appeal's linked schedule and recalculates hearing sequence; conditionally calls `popcasestatus` to revert case status and `popfilelocation` to revert file location when the deleted appeal affected them. |
| [BR-PKG1-005](../business-rules.md#br-pkg1-005-assigned-attorney-resolution-fallback-order) | Assigned attorney resolution fallback order | `get_asgn_atty` / `get_asgn_atty_id` resolve the case's assigned attorney | Uses the attorney from the case's most recent appeal if present; otherwise the attorney assigned to the case's scheduled hearing; otherwise the assigned other-firm attorney name. |
| [BR-PKG1-006](../business-rules.md#br-pkg1-006-case-number-display-priority) | Case number display priority | `get_case_num` resolves the case number to display | Returns the first non-null case number in order: WCB, DCBS, OWCP, OALJ, BRB. |
| [BR-PKG1-007](../business-rules.md#br-pkg1-007-oar-due-date-and-case-weight-calculation) | OAR due date and case weight calculation | `UPDATE_LEGALCASE_REQUEST` sets request fields on a legal case | Sets `OAR_DUE_DATE` to postmark date + 10 days only when no existing reconsideration-flagged request issue exists for the case; sets `CASE_WEIGHT_VALUE` to 3 when the stress-claim flag is `'Y'`, else 1. |
| [BR-PKG1-008](../business-rules.md#br-pkg1-008-lce-excused-upsert-logic) | LCE excused flag uses conditional upsert logic | `update_lce_Excused` sets the LCE excused flag for a case | Updates the existing LCE row only if the excused flag actually changed; if no LCE row exists and the new flag is `'Y'`, inserts a new LCE row instead. |
| [BR-PKG1-009](../business-rules.md#br-pkg1-009-oar-and-reconsideration-mail-eligibility) | OAR and reconsideration mail eligibility | `mailDateList` selects cases pending mail processing | A case is OAR-eligible when `MAIL_OAR_FLAG='Y'`, not closed, `OAR_DUE_DATE IS NOT NULL`, and `OAR_MAILED_DATE IS NULL`; reconsideration-eligible when `RECON_LETTER_MAILED_DATE IS NOT NULL` and `RECON_PACKET_RECEIVED_DATE IS NULL`. |
| [BR-PKG1-010](../business-rules.md#br-pkg1-010-consolidated-secondary-cases-inherit-mail-date-from-primary) | Consolidated secondary cases inherit mail date from primary | `setNoMailDate` is called for a consolidated secondary case | The mail date is not set independently on the secondary; it is inherited from the consolidation primary case. |
| [BR-PKG1-011](../business-rules.md#br-pkg1-011-file-location-is-versioned-by-move-date) | File location is versioned by move date | `getFileLocation` / `popfilelocation` access `LEGAL_FILE_LOCATION` history | The most recent row ordered by `move_date` descending is authoritative; `popfilelocation` reverts location by deleting that most recent row. |
| [BR-PKG1-012](../business-rules.md#br-pkg1-012-consolidated-secondary-cases-remain-visible-when-closed) | Consolidated secondary cases remain visible when closed | `getConsolidatedCases` filters the case list | A case is included if it is not closed and not a consolidated secondary, OR if it is a consolidated secondary regardless of its closed status. |
| [BR-PKG1-013](../business-rules.md#br-pkg1-013-consolidation-link-changes-migrate-or-reset-hearing-sequence) | Consolidation link changes migrate or reset hearing sequence | `UpdateConsolidatedCase` changes a primary/secondary link | Reassigning a secondary to a new primary migrates its most recent hearing schedule to the new primary; removing the link (`aConsolFlag='N'`) recalculates `MOST_RECENT_HEARING_SEQ_NUM` on the now-independent secondary from its own schedule. |
| [BR-PKG1-014](../business-rules.md#br-pkg1-014-final-outcome-recording-drives-case-closure-eligibility) | Final outcome recording drives case closure eligibility | `updateCaseOutcome` / `SetFinalOutcome` record a case outcome | When a qualifying final order type/outcome is recorded, the case outcome becomes the case's outcome of record, driving closure eligibility. |
| [BR-PKG1-015](../business-rules.md#br-pkg1-015-outcome-issue-and-outcome-staff-use-upsert-semantics) | Outcome issue and outcome staff use upsert semantics | `updateOutcomeIssue` / `updateOutcomeStaff` are called with a sequence number | Updates the row if the sequence number already exists in `OUTCOME_ISSUE`/`OUTCOME_STAFF`, otherwise inserts a new row. |

## Operations

| Operation | Description | Trigger |
| --- | --- | --- |
| Legal Case CRUD Lifecycle | Creation, retrieval, update, and deletion of a `LEGAL_CASE` record, including its request fields, case number resolution, and hearing-sequence/attorney assignment. | A new legal proceeding is opened, edited, or closed against a claim in LegalNet. |
| Case Search | Search for legal cases by claim, case number, status, or other criteria, excluding closed cases unless explicitly requested. | User performs a case search in the LegalNet UI. |
| Hearing Schedule Lifecycle | Creation, update, and deletion of `SCHEDULE` rows (hearings, conference calls, mediations), with automatic recalculation of the case's most recent hearing sequence number. | A hearing or related event is scheduled, rescheduled, or cancelled for a legal case. |
| Appeal Lifecycle | Creation, update, and deletion of `APPEAL` rows and their extensions, with cascading reversion of case status and file location when an appeal is deleted. | An appeal is filed, amended, extended, or withdrawn for a legal case. |
| Case Outcome Lifecycle | Recording of `CASE_OUTCOME`, `OUTCOME_ISSUE`, and `OUTCOME_STAFF` rows, culminating in marking a final outcome of record. | An order is signed/filed or a hearing outcome is recorded for a legal case. |
| Consolidation Lifecycle | Linking and unlinking of consolidated secondary cases to a primary case, with cascading status updates and hearing-sequence migration/reset. | Related claims/cases are consolidated for joint legal handling, or a consolidation link is removed. |
| File Location Lifecycle | Tracking of the physical/custodial location of a legal case file over time via versioned `LEGAL_FILE_LOCATION` rows. | The case file is transferred between staff, attorneys, or storage locations. |
| Mail Date / OAR Lifecycle | Tracking and processing of Order on Appeal (OAR) and reconsideration-letter mail dates, including eligibility list generation and consolidated-case inheritance. | An OAR or reconsideration letter becomes due, is mailed, or a related packet is received. |
| Attorney Profile & Assignment Lifecycle | Maintenance of attorney profile records (claim-type eligibility, caseload, hearing locations) and assignment of attorneys to cases, hearings, and appeals. | An attorney's profile changes, or an attorney is assigned/reassigned to a case, hearing, or appeal. |
| Case Note Lifecycle | Creation, update, and deletion of free-text `CASE_NOTE` entries against a legal case. | A user records a note on a legal case. |
| Case Cost Lifecycle | Creation, update, and deletion of `CASE_COST` billing records associated with a legal case. | A legal service cost/billing entry is recorded, corrected, or removed for a case. |

## Source Code

<details>
<summary>PL/SQL package specification</summary>

```sql
PACKAGE                   Pk80001_Legalnet AS

TYPE ref_cursor IS REF CURSOR;

 PROCEDURE findCase(recordset OUT ref_cursor, in_num IN VARCHAR2);

FUNCTION get_hearing_date (in_case_seq IN LEGAL_CASE.legal_case_seq_num%TYPE)
  RETURN DATE;

FUNCTION get_consolidated_hearing_seq (in_case_seq IN LEGAL_CASE.legal_case_seq_num%TYPE)
   RETURN NUMBER;

FUNCTION get_hearingID (in_case_seq IN LEGAL_CASE.legal_case_seq_num%TYPE)
  RETURN NUMBER;

FUNCTION get_asgn_atty(IN_case_Seq_num IN LEGAL_CASE.legal_case_seq_num%TYPE)
  RETURN VARCHAR2;

FUNCTION get_asgn_atty_id(IN_case_Seq_num IN LEGAL_CASE.legal_case_seq_num%TYPE)
  RETURN VARCHAR2;

FUNCTION get_atty_mngr_email(aLegalCaseNum IN NUMBER) RETURN VARCHAR2;

FUNCTION get_mailid_for_emplid(inEmplid IN VARCHAR2) RETURN VARCHAR2;

FUNCTION get_case_num(aLegalCaseNum IN NUMBER) RETURN VARCHAR2;

Function InsertLegalCase(LegalCaseSeqNum in LEGAL_CASE.LEGAL_CASE_SEQ_NUM%TYPE,
                         CaseTypeCode IN LEGAL_CASE.REQUEST_TYPE_CODE%TYPE,
                         RequestTypeCode in LEGAL_CASE.REQUEST_TYPE_CODE%TYPE,
                         WcbNum in LEGAL_CASE.WCB_NUM%TYPE,
                         DcbsNum in LEGAL_CASE.DCBS_NUM%Type,
                         CaseStatusCode in LEGAL_CASE.CASE_STATUS_CODE%Type,
                         claimID        IN LEGAL_CASE.claim_id%TYPE,
                         RequestDate    IN LEGAL_CASE.request_date%TYPE,
                         WorkerAttyProvLocID in LEGAL_CASE.WORKER_ATTY_PROV_LOC_ID%Type,
                         WorkerAttyFirmProvLocID in LEGAL_CASE.WORKER_ATTY_FIRM_PROV_LOC_ID%Type,
                         OarDueDate      IN LEGAL_CASE.OAR_DUE_DATE%type,
                         OarMailedDate   IN LEGAL_CASE.OAR_MAILED_DATE%type,
                         LegalFileCreateDate IN LEGAL_CASE.LEGAL_FILE_CREATE_DATE%Type,
                         CaseWeightValue  IN LEGAL_CASE.CASE_WEIGHT_VALUE%Type,
                         AcknowledgeDate  IN LEGAL_CASE.ACKNOWLEDGE_DATE%type,
                         RequestNewWcbNumFlag IN LEGAL_CASE.REQUEST_ALL_DAY_HEARING_FLAG%Type,
                         RequestedByCode      IN LEGAL_CASE.REQUESTED_BY_CODE%TYPE,
                         RequestReceiptDate   in LEGAL_CASE.REQUEST_RECEIPT_DATE%Type,
                         RequestPostmarkDate  in LEGAL_CASE.REQUEST_POSTMARK_DATE%type,
                         RequestAllDayHearingFlag in LEGAL_CASE.REQUEST_ALL_DAY_HEARING_FLAG%type,
                         StressClaimFlag      in LEGAL_CASE.STRESS_CLAIM_FLAG%type,
                         ReconLetterMailedDate in LEGAL_CASE.RECON_LETTER_MAILED_DATE%type,
                         ReconPacketReceivedDate in LEGAL_CASE.RECON_PACKET_RECEIVED_DATE%type,
                         DismissalDate         in LEGAL_CASE.DISMISSAL_DATE%Type,
                         SettlementDate        in LEGAL_CASE.SETTLEMENT_DATE%Type,
                         SendDefFlag           in LEGAL_CASE.SEND_DEF_FLAG%type,
                         DefSentDate           in LEGAL_CASE.DEF_SENT_DATE%type,
                         EmployerEvaluationNum in LEGAL_CASE.EMPLOYER_EVALUATION_NUM%type,
                         ConsolidateCaseFlag   in LEGAL_CASE.CONSOLIDATE_CASE_FLAG%type,
                         OwcpNum               in LEGAL_CASE.OWCP_NUM%Type,
                         OaljNum               in LEGAL_CASE.OALJ_NUM%type,
                         BrbNum                in LEGAL_CASE.BRB_NUM%type,
                         CreateDate            in LEGAL_CASE.CREATE_DATE%type,
                         CreateUser            in LEGAL_CASE.CREATE_USER%type,
                         MostRecentHearingSeqNum in LEGAL_CASE.MOST_RECENT_HEARING_SEQ_NUM%type,
                         ClaimImageAlertsFlag   in LEGAL_CASE.CLAIM_IMAGE_ALERTS_FLAG%type)
         Return Number;

Function UpdateLegalCase(LegalCaseSeqNum in LEGAL_CASE.LEGAL_CASE_SEQ_NUM%TYPE,
                         CaseTypeCode IN LEGAL_CASE.REQUEST_TYPE_CODE%TYPE,
                         RequestTypeCode in LEGAL_CASE.REQUEST_TYPE_CODE%TYPE,
                         WcbNum in LEGAL_CASE.WCB_NUM%TYPE,
                         DcbsNum in LEGAL_CASE.DCBS_NUM%Type,
                         CaseStatusCode in LEGAL_CASE.CASE_STATUS_CODE%Type,
                         claimID        IN LEGAL_CASE.claim_id%TYPE,
                         RequestDate    IN LEGAL_CASE.request_date%TYPE,
                         WorkerAttyProvLocID in LEGAL_CASE.WORKER_ATTY_PROV_LOC_ID%Type,
                         WorkerAttyFirmProvLocID in LEGAL_CASE.WORKER_ATTY_FIRM_PROV_LOC_ID%Type,
                         OarDueDate      IN LEGAL_CASE.OAR_DUE_DATE%type,
                         OarMailedDate   IN LEGAL_CASE.OAR_MAILED_DATE%type,
                         LegalFileCreateDate IN LEGAL_CASE.LEGAL_FILE_CREATE_DATE%Type,
                         CaseWeightValue  IN LEGAL_CASE.CASE_WEIGHT_VALUE%Type,
                         AcknowledgeDate  IN LEGAL_CASE.ACKNOWLEDGE_DATE%type,
                         RequestNewWcbNumFlag IN LEGAL_CASE.REQUEST_ALL_DAY_HEARING_FLAG%Type,
                         RequestedByCode      IN LEGAL_CASE.REQUESTED_BY_CODE%TYPE,
                         RequestReceiptDate   in LEGAL_CASE.REQUEST_RECEIPT_DATE%Type,
                         RequestPostmarkDate  in LEGAL_CASE.REQUEST_POSTMARK_DATE%type,
                         RequestAllDayHearingFlag in LEGAL_CASE.REQUEST_ALL_DAY_HEARING_FLAG%type,
                         StressClaimFlag      in LEGAL_CASE.STRESS_CLAIM_FLAG%type,
                         ReconLetterMailedDate in LEGAL_CASE.RECON_LETTER_MAILED_DATE%type,
                         ReconPacketReceivedDate in LEGAL_CASE.RECON_PACKET_RECEIVED_DATE%type,
                         DismissalDate         in LEGAL_CASE.DISMISSAL_DATE%Type,
                         SettlementDate        in LEGAL_CASE.SETTLEMENT_DATE%Type,
                         SendDefFlag           in LEGAL_CASE.SEND_DEF_FLAG%type,
                         DefSentDate           in LEGAL_CASE.DEF_SENT_DATE%type,
                         EmployerEvaluationNum in LEGAL_CASE.EMPLOYER_EVALUATION_NUM%type,
                         ConsolidateCaseFlag   in LEGAL_CASE.CONSOLIDATE_CASE_FLAG%type,
                         OwcpNum               in LEGAL_CASE.OWCP_NUM%Type,
                         OaljNum               in LEGAL_CASE.OALJ_NUM%type,
                         BrbNum                in LEGAL_CASE.BRB_NUM%type,
                         UpdateDate            in LEGAL_CASE.CREATE_DATE%type,
                         UpdateUser            in LEGAL_CASE.CREATE_USER%type,
                         MostRecentHearingSeqNum in LEGAL_CASE.MOST_RECENT_HEARING_SEQ_NUM%type,
                         ClaimImageAlertsFlag   in LEGAL_CASE.CLAIM_IMAGE_ALERTS_FLAG%type)
         Return Number;
PROCEDURE CREATE_REQUEST_ISSUE(in_legal_case_seq IN LEGAL_CASE.legal_Case_seq_num%TYPE,
                               in_issue_code     IN REQUEST_ISSUE.REQUEST_ISSUE_CODE%TYPE,
                               in_issue_text     IN REQUEST_ISSUE.REQUEST_ISSUE_TEXT%TYPE);

FUNCTION get_case_issue_codes (in_case_seq_num NUMBER) RETURN VARCHAR2;

FUNCTION get_case_issue_text (in_case_seq_num NUMBER) RETURN VARCHAR2;

TYPE search_rec IS RECORD(
       wcb_num          VARCHAR2(15),
       case_status      VARCHAR2(65),
       atty_name        VARCHAR2(50),
       hearing_date     VARCHAR2(10),
       REQUEST_ISSUE    VARCHAR2(30),
       requested_by     VARCHAR2(65),
       legal_case_seq_num NUMBER,
       claim_id           NUMBER);

 TYPE search_cursor IS REF CURSOR RETURN search_rec;

PROCEDURE Casesearch(recordset OUT search_cursor,
                        claimIdsCommaDelimited IN VARCHAR2,
                        includeClosed IN VARCHAR2);

 PROCEDURE updateCaseStatus(aLegalCaseNum IN NUMBER, newStatus IN VARCHAR2,
            updateDate IN DATE, updateUser IN VARCHAR2);

 PROCEDURE updateHearingSeqNum(aLegalCaseNum IN NUMBER);

 PROCEDURE getLCE(LegalCaseID IN NUMBER,
       Recordset OUT ref_cursor);

 Function updateLCE(EvaluationID IN NUMBER, IsExcused IN VARCHAR2, dueDate IN DATE,
              filedDate IN DATE, AdjusterEmployeeID IN VARCHAR2, casetransferDate IN DATE,
              updateDate IN DATE, updateUser IN VARCHAR2,
              AsgnAttyEmplid IN VARCHAR2)
              return number;

 Function insertLCE(EvaluationID IN NUMBER, LegalCaseID in number,
              IsExcused IN VARCHAR2, dueDate IN DATE,
              filedDate IN DATE, AdjusterEmployeeID IN VARCHAR2, casetransferDate IN DATE,
              createDate IN DATE, createUser IN VARCHAR2,
              AsgnAttyEmplid IN VARCHAR2)
              return number;

 PROCEDURE getSchedule(LegalCaseSeqNum IN NUMBER,
           recordset OUT ref_cursor);

 Function updateSchedule(ScheduleSeqNum IN NUMBER,
    ScheduleDate IN DATE, ScheduleTime IN VARCHAR2, AljPersonSeqNum IN NUMBER,
    HearingLocationSeqNum IN NUMBER, AsgnAttorneyEmplid IN VARCHAR2, DurationCode IN VARCHAR2,
    ScheduleTypeCode IN VARCHAR2, ScheduleStatusCode IN VARCHAR2, KeepAttorneyFlag IN VARCHAR2,
    AssignedDate IN DATE, ReminderDate IN DATE, ScheduleText IN VARCHAR2,
    UpdateDate IN DATE, UpdateUser IN VARCHAR2,
    AsgnOtherFirmSeqNum IN NUMBER, AsgnPersonSeqNum IN NUMBER)
    return Number;

 Function insertSchedule(ScheduleSeqNum IN NUMBER, LegalCaseSeqNum IN NUMBER,
      ScheduleDate IN DATE, ScheduleTime IN VARCHAR2, AljPersonSeqNum IN NUMBER,
      HearingLocationSeqNum IN NUMBER, AsgnAttorneyEmplid IN VARCHAR2, DurationCode IN VARCHAR2,
      ScheduleTypeCode IN VARCHAR2, ScheduleStatusCode IN VARCHAR2, KeepAttorneyFlag IN VARCHAR2,
      AssignedDate IN DATE, ReminderDate IN DATE, ScheduleText IN VARCHAR2,
      createDate IN DATE, createUser IN VARCHAR2,
      asgnOtherFirmSeqNum IN NUMBER, asgnPersonSeqNum IN NUMBER)
      return number;

 Function deleteSchedule(ScheduleSeqNum IN NUMBER)
 return number;

 PROCEDURE getLegalCase(LegalCaseSeqNum IN NUMBER,
       ClaimId in Number,
       recordset IN OUT ref_cursor);

 FUNCTION getClaimImageAlertsUpdateDate(aLegalCaseNum IN NUMBER)
     RETURN DATE;

PROCEDURE UPDATE_LEGALCASE_REQUEST(aLegalCaseSeq IN NUMBER,
                                   aIssueType IN LEGAL_CASE.REQUEST_TYPE_CODE%TYPE,
                                   aWCBRequested IN LEGAL_CASE.request_new_wcb_num_flag%TYPE,
                                   aStress IN LEGAL_CASE.stress_claim_flag%TYPE,
                                   aRequestedBy IN LEGAL_CASE.REQUESTED_BY_CODE%TYPE,
                                   aRequestDate IN VARCHAR2,
                                   aReceivedDate IN VARCHAR2,
                                   aPostmarkDate IN VARCHAR2,
                                   aAttyFirmProvID IN NUMBER,
                                   aReconLetter IN VARCHAR2,
                                   aAttyProvID IN NUMBER,
                                   aLCEExcused IN VARCHAR2,
                                   aOWCP IN VARCHAR2,
                                   aUserID IN VARCHAR2);

PROCEDURE update_lce_Excused(aLegalCaseSeq IN NUMBER,
                             aLCEExcused IN VARCHAR2,
                             aUserID IN VARCHAR2);


 PROCEDURE setFileCreateDate(seqNums IN VARCHAR2, inDate IN DATE,
           userid IN VARCHAR2);

 PROCEDURE mailDateList(recordset OUT ref_cursor, inType IN VARCHAR2);

 PROCEDURE setMailDate(seqNums IN VARCHAR2, inDate IN DATE,
           inType IN VARCHAR2, userid IN VARCHAR2);

 Function setNoMailDate(InseqNum IN NUMBER, inDate IN DATE,
           inType IN VARCHAR2, userid IN VARCHAR2)
          RETURN NUMBER;

  Function  setMailOARFlag(InseqNum IN NUMBER, userid IN VARCHAR2)
       RETURN NUMBER;

 Function insertAppeal(AppealID IN NUMBER, LegalCaseID IN NUMBER,
              reviewLevel IN VARCHAR2, dueDate IN DATE, filedDate IN DATE,
              PetitionerRespondentCode IN VARCHAR2, approvedFlag IN VARCHAR2,
              attyEmplid IN VARCHAR2,
              brief1DueDate IN DATE, brief1FiledDate IN DATE,
              brief2DueDate IN DATE, brief2FiledDate IN DATE,
              brief3DueDate IN DATE, brief3FiledDate IN DATE,
              appealNum IN VARCHAR2, ScheduleID IN NUMBER,
              createDate IN DATE, createUser IN VARCHAR2)
         RETURN NUMBER;

 FUNCTION updateAppeal(AppealID IN NUMBER, LegalCaseID IN NUMBER,
              reviewLevel IN VARCHAR2, dueDate IN DATE, filedDate IN DATE,
              PetitionerRespondentCode IN VARCHAR2, approvedFlag IN VARCHAR2,
              attyEmplid IN VARCHAR2,
              brief1DueDate IN DATE, brief1FiledDate IN DATE,
              brief2DueDate IN DATE, brief2FiledDate IN DATE,
              brief3DueDate IN DATE, brief3FiledDate IN DATE,
              appealNum IN VARCHAR2, ScheduleID IN NUMBER,
              updateDate IN DATE, updateUser IN VARCHAR2)
           RETURN NUMBER;

     PROCEDURE getAppeal(LegalCaseID IN NUMBER,
       Recordset OUT ref_cursor);

    PROCEDURE getAppealExtension(appealSeqNum IN NUMBER,
       Recordset OUT ref_cursor);

    Function updateAppealExtension(
         appealExtSeqNum IN NUMBER,
         appealSeqNum    IN NUMBER,
         requestDate     IN DATE,
         daysRequested   IN NUMBER)
          RETURN NUMBER;

     Function insertAppealExtension(
         appealExtSeqNum IN NUMBER,
         appealSeqNum    IN NUMBER,
         requestDate     IN DATE,
         daysRequested   IN NUMBER)
       RETURN NUMBER;

    Function deleteappeal (inlegalcaseseqnum IN NUMBER, inappealseqnum IN NUMBER)
     RETURN NUMBER;

	PROCEDURE popcasestatus(inlegalcaseseqnum IN LEGAL_CASE.legal_case_seq_num%TYPE);

	PROCEDURE popfilelocation(inlegalcaseseqnum IN LEGAL_CASE.legal_case_seq_num%TYPE);

    PROCEDURE getBoardLog(Recordset OUT ref_cursor);

    PROCEDURE updateAppealAtty(appealSeqNum IN NUMBER,
       attyEmplid IN VARCHAR2);

   PROCEDURE getAcknowledgeList(recordset OUT ref_cursor, inType IN VARCHAR2);

   Function updateAcknowledged(inType IN VARCHAR2, inNum IN VARCHAR2,
       inDate IN DATE, CaseId IN NUMBER)
        RETURN NUMBER;

   PROCEDURE updateDEFfields(aLegalCaseNum IN NUMBER, inNum IN VARCHAR2,
       inDate IN DATE, inFlag IN VARCHAR2, inUser IN VARCHAR2);


    PROCEDURE getFileLocation(LegalCaseID IN NUMBER,
       Recordset OUT ref_cursor);

    Function updateFileLocation(
         LegalFileLocationID   IN NUMBER,
         LegalCaseID           IN NUMBER,
         LocationPersonEmplid  IN VARCHAR2,
         LegalFileLocationCode IN VARCHAR2,
         moveDate              IN DATE
        ) return number;

     Function insertFileLocation(
         LegalFileLocationID   IN NUMBER,
         LegalCaseID           IN NUMBER,
         LocationPersonEmplid  IN VARCHAR2,
         LegalFileLocationCode IN VARCHAR2,
         moveDate              IN DATE,
         createDate            in date,
         createUser            in varchar2)
       return number;

    PROCEDURE getAttyProfile(Recordset OUT ref_cursor);

    Function updateAttyProfile(
       profileEmplid      IN   VARCHAR2,
       EffDate            IN   DATE,
       ExpDate            IN   DATE,
       CcuClaimsFlag      IN   VARCHAR2,
       PtdClaimsFlag      IN   VARCHAR2,
       PpdClaimsFlag      IN   VARCHAR2,
       ConfidentialClaimsFlag IN VARCHAR2,
       HealthIssueClaimsFlag  IN VARCHAR2,
       DcbsClaimsFlag         IN VARCHAR2,
       FederalClaimsFlag      IN VARCHAR2,
       LoadPercent            IN NUMBER,
       PrimaryHearLocCode     IN VARCHAR2,
       SecondaryHearLocCode   IN VARCHAR2,
       ClaimTeamCode          IN VARCHAR2,
       AttorneyTypeCode       IN VARCHAR2,
       SecretaryEmplid        IN VARCHAR2,
       ManagerEmplid          IN VARCHAR2,
       TertiaryHearLocCode    IN VARCHAR2,
       osbNumber              IN VARCHAR2
       ) RETURN NUMBER;

     Function insertAttyProfile(
       profileEmplid      IN   VARCHAR2,
       EffDate            IN   DATE,
       ExpDate            IN   DATE,
       CcuClaimsFlag      IN   VARCHAR2,
       PtdClaimsFlag      IN   VARCHAR2,
       PpdClaimsFlag      IN   VARCHAR2,
       ConfidentialClaimsFlag IN VARCHAR2,
       HealthIssueClaimsFlag  IN VARCHAR2,
       DcbsClaimsFlag         IN VARCHAR2,
       FederalClaimsFlag      IN VARCHAR2,
       LoadPercent            IN NUMBER,
       PrimaryHearLocCode     IN VARCHAR2,
       SecondaryHearLocCode   IN VARCHAR2,
       ClaimTeamCode          IN VARCHAR2,
       AttorneyTypeCode       IN VARCHAR2,
       SecretaryEmplid        IN VARCHAR2,
       ManagerEmplid          IN VARCHAR2,
       TertiaryHearLocCode    IN VARCHAR2,
       osbNumber              IN VARCHAR2
        ) RETURN NUMBER;

   TYPE attySched_rec IS RECORD(
       attorney_schedule_seq_num  NUMBER,
       schedule_emplid            VARCHAR2(11),
       eff_date                   DATE,
       exp_date                   DATE,
       ATTORNEY_SCHEDULE_TYPE_CODE VARCHAR2(5),
       DURATION_CODE               VARCHAR2(5));

    TYPE attySched_cursor IS REF CURSOR RETURN attySched_rec;

    PROCEDURE getAttySchedule(Recordset OUT attySched_cursor);

    PROCEDURE updateAttySchedule(
         attySchedSeqNum IN NUMBER,
         scheduleEmplid  IN VARCHAR2,
         effDate         IN DATE,
         expDate         IN DATE,
         typeCode        IN VARCHAR2,
         durationCode    IN VARCHAR2);

     PROCEDURE insertAttySchedule(
       attySchedSeqNum IN NUMBER,
         scheduleEmplid  IN VARCHAR2,
         effDate         IN DATE,
         expDate         IN DATE,
         typeCode        IN VARCHAR2,
         durationCode    IN VARCHAR2);

     PROCEDURE deleteAttySchedule(attySchedSeqNum IN NUMBER);

PROCEDURE getConsolidatedCases(recordset OUT ref_cursor,
                        claimIdsCommaDelimited IN VARCHAR2);

 PROCEDURE UpdateConsolidatedCase(aConsolidatedSeq IN CONSOLIDATED_CASE.consolidated_case_seq_num%TYPE,
                                   aPrimarySeq IN CONSOLIDATED_CASE.primary_case_seq_num%TYPE,
                                   aSecondarySeq IN CONSOLIDATED_CASE.secondary_case_seq_num%TYPE,
                                   aConsolFlag IN VARCHAR2,
                                   UpdateDate in DATE,
                                   UpdateUser in Varchar2);
TYPE attorney_rec IS RECORD(
     atty_name VARCHAR2(50),
     legal_person_seq_num LEGAL_PERSON.legal_person_seq_num%TYPE);
TYPE attorney_cursor IS REF CURSOR RETURN attorney_rec;

PROCEDURE getAttorneyNames(recordset OUT attorney_cursor);

PROCEDURE insertLetterRequest(
         letterTypeCode  IN VARCHAR2,
         aLegalCaseNum   IN NUMBER,
         holdDate        IN DATE,
		 aClaimId        IN NUMBER,
		 createUser		 IN VARCHAR2,
         requestNum      IN OUT NUMBER);

TYPE HearingInfo_rec IS RECORD(
schedule_seq_num SCHEDULE.schedule_seq_num%TYPE,
schedule_date SCHEDULE.schedule_date%TYPE,
schedule_time SCHEDULE.schedule_time%TYPE,
DURATION_CODE SCHEDULE.DURATION_CODE%TYPE,
keep_attorney_flag NUMBER(1,0),
assigned_date SCHEDULE.assigned_date%TYPE,
reminder_date SCHEDULE.reminder_date%TYPE,
schedule_text SCHEDULE.schedule_text%TYPE,
atty_mailid adp_employee.user_id%TYPE,
sec_mailid adp_employee.user_id%TYPE,
sec_name VARCHAR2(50),
alj_name LEGAL_PERSON.last_name%TYPE,
secretary_emplid ATTORNEY_PROFILE.secretary_emplid%TYPE,
HEARING_LOCATION_CODE HEARING_LOCATION.HEARING_LOCATION_CODE%TYPE,
hearing_loc_city VARCHAR2(50),
location_name HEARING_LOCATION.location_name%TYPE,
hearing_status SCHEDULE_STATUS_CODE.code_descr%TYPE,
all_day_hearing_flag NUMBER(1,0));
TYPE HearingCursor IS REF CURSOR RETURN HearingInfo_rec;

PROCEDURE getHearingInfo(in_HearingSeq IN SCHEDULE.schedule_seq_num%TYPE,
                         recordset    OUT HearingCursor);

TYPE EmployerAddr_rec IS RECORD(
policy_name     POLICY.policy_name%TYPE,
Loc_addr1       LOCATION.loc_addr1%TYPE,
loc_addr2       LOCATION.loc_Addr2%TYPE,
loc_city        LOCATION.loc_city%TYPE,
loc_state       LOCATION.loc_state%TYPE,
loc_zip         LOCATION.loc_zip%TYPE,
loc_zip4        LOCATION.loc_zip4%TYPE,
mail_addr_flag  LOCATION.mail_addr_flag%TYPE,
loc_phone       VARCHAR2(20),
loc_seq_num     LOCATION.loc_seq_num%TYPE);
TYPE EmployerCursor IS REF CURSOR RETURN EmployerAddr_rec;
PROCEDURE getEmployerAddr(in_Policy IN POLICY.policy_num%TYPE,
                         recordset    OUT EmployerCursor);
TYPE OtherFirmList_rec IS RECORD(
type_cd         VARCHAR2(10),
legal_case_employer_seq_num LEGAL_CASE_EMPLOYER.legal_case_employer_seq_num%TYPE,
employer_other_firm_seq_num OTHER_FIRM.other_firm_seq_num%TYPE,
employer_discovery_flag NUMBER(1,0),
attorney_discovery_flag NUMBER(1,0),
firm_name OTHER_FIRM.firm_name%TYPE,
OTHER_FIRM_TYPE_CODE OTHER_FIRM.OTHER_FIRM_TYPE_CODE%TYPE,
addr1 OTHER_FIRM.addr1%TYPE,
addr2 OTHER_FIRM.addr2%TYPE,
city OTHER_FIRM.city%TYPE,
state OTHER_FIRM.state%TYPE,
zip OTHER_FIRM.zip%TYPE,
zip4 OTHER_FIRM.zip4%TYPE,
PHONE OTHER_FIRM.PHONE%TYPE,
fax   OTHER_FIRM.fax%TYPE);
TYPE OtherFirmListCursor IS REF CURSOR RETURN OtherFirmList_rec;
PROCEDURE getOtherFirmList(aLegalCaseNum IN NUMBER,
                           recordset OUT OtherFirmListCursor);
TYPE employee_rec IS RECORD(
    EMPLID adp_employee.emplid%TYPE,
	LAST_NAME adp_employee.last_name%TYPE,
	FIRST_NAME adp_employee.first_name%TYPE,
	MIDDLE_NAME adp_employee.middle_name%TYPE,
	EMAIL_ADDRESS adp_employee.EMAIL_ADDRESS%TYPE,
	EMPL_STATUS adp_employee.empl_status%TYPE,
	PHONE adp_employee.PHONE%TYPE,
	PHONE_EXTENSION adp_employee.phone_extension%TYPE,
	LOCATION adp_employee.LOCATION%TYPE,
	PREFERRED_NAME adp_employee.preferred_name%TYPE,
	DEPTID adp_employee.deptid%TYPE,
	DESCR adp_employee.descr%TYPE,
	REPORTS_TO adp_employee.reports_to%TYPE,
	USER_ID adp_employee.user_id%TYPE);
TYPE EmployeeCursor IS REF CURSOR RETURN Employee_rec;

PROCEDURE GetEmployeebyMailid(in_MailId IN VARCHAR,
                      recordset OUT EmployeeCursor);

FUNCTION GetEmployeeName(in_mailid IN VARCHAR)
  RETURN VARCHAR2;

PROCEDURE IsLetterRequested(aLegalCaseNum IN NUMBER,
                           aLetterType   IN VARCHAR2,
                           out_requested OUT VARCHAR2);

TYPE case_cost_rec IS RECORD(
    case_cost_seq_num_out  CASE_COST.case_cost_seq_num%TYPE,
  	legal_case_seq_num_out CASE_COST.legal_case_seq_num%TYPE,
	service_from_date_out  CASE_COST.service_from_date%TYPE,
	service_to_date_out    CASE_COST.service_to_date%TYPE,
	service_code_out       CASE_COST.SERVICE_CODE%TYPE,
	amount_paid_out        CASE_COST.amount_paid%TYPE,
	amount_billed_out      CASE_COST.amount_billed%TYPE,
    amount_per_hour_out    CASE_COST.amount_per_hour%TYPE,
    hours_billed_out       CASE_COST.hours_billed%TYPE,
	prepayment_flag_out    VARCHAR2(5),
	authorized_date_out    CASE_COST.authorized_date%TYPE,
    payee_firm_prov_loc_id_out CASE_COST.payee_firm_prov_loc_id%TYPE,
    payee_person_prov_loc_id_out CASE_COST.payee_person_prov_loc_id%TYPE,
	authorizer_emplid_out  CASE_COST.authorizer_emplid%TYPE,
	cancelled_by_code_out  CASE_COST.CANCELLED_BY_CODE%TYPE,
    create_date            DATE,
    create_user         VARCHAR2(30),
    update_date         DATE,
    update_user         VARCHAR2(30));

TYPE CaseCostCursor IS REF CURSOR RETURN case_cost_rec;

PROCEDURE GetCaseCost(aLegalCaseNum IN  NUMBER,
                      recordset OUT CaseCostCursor);
/********************************************
** UPDATE PROCEDURE FOR TABLE "LEGAL.CASE_COST"
** DATE: 28-JUL-03 13:22:19
*********************************************/

PROCEDURE case_cost_update
(
	case_cost_seq_num_in IN CASE_COST.case_cost_seq_num%TYPE,
	legal_case_seq_num_in IN CASE_COST.legal_case_seq_num%TYPE,
	service_from_date_in IN CASE_COST.service_from_date%TYPE,
	service_to_date_in IN CASE_COST.service_to_date%TYPE,
	service_code_in IN CASE_COST.SERVICE_CODE%TYPE,
	amount_paid_in IN CASE_COST.amount_paid%TYPE,
	amount_billed_in IN CASE_COST.amount_billed%TYPE,
    amount_per_hour_in IN CASE_COST.amount_per_hour%TYPE,
    hours_billed_in IN    CASE_COST.hours_billed%TYPE,
	prepayment_flag_in IN CASE_COST.prepayment_flag%TYPE,
	authorized_date_in IN CASE_COST.authorized_date%TYPE,
	payee_firm_prov_loc_id_in IN CASE_COST.PAYEE_firm_PROV_LOC_ID%TYPE,
    payee_person_prov_loc_id_in IN CASE_COST.payee_person_prov_loc_id%TYPE,
	authorizer_emplid_in IN CASE_COST.authorizer_emplid%TYPE,
	cancelled_by_code_in IN CASE_COST.CANCELLED_BY_CODE%TYPE,
    update_date_in IN CASE_COST.update_date%TYPE,
	update_user_in IN CASE_COST.update_user%TYPE);

/********************************************
** INSERT PROCEDURE FOR TABLE "LEGAL.CASE_COST"
** DATE: 28-JUL-03 13:22:19
*********************************************/
PROCEDURE case_cost_insert
(
	case_cost_seq_num_in IN CASE_COST.case_cost_seq_num%TYPE,
	legal_case_seq_num_in IN CASE_COST.legal_case_seq_num%TYPE,
	service_from_date_in IN CASE_COST.service_from_date%TYPE,
	service_to_date_in IN CASE_COST.service_to_date%TYPE,
	service_code_in IN CASE_COST.SERVICE_CODE%TYPE,
	amount_paid_in IN CASE_COST.amount_paid%TYPE,
	amount_billed_in IN CASE_COST.amount_billed%TYPE,
    amount_per_hour_in IN CASE_COST.amount_per_hour%TYPE,
    hours_billed_in IN    CASE_COST.hours_billed%TYPE,
	prepayment_flag_in IN CASE_COST.prepayment_flag%TYPE,
	authorized_date_in IN CASE_COST.authorized_date%TYPE,
	payee_firm_prov_loc_id_in IN CASE_COST.PAYEE_firm_PROV_LOC_ID%TYPE,
    payee_person_prov_loc_id_in IN CASE_COST.payee_person_prov_loc_id%TYPE,
   	authorizer_emplid_in IN CASE_COST.authorizer_emplid%TYPE,
	cancelled_by_code_in IN CASE_COST.CANCELLED_BY_CODE%TYPE,
    create_date_in IN CASE_COST.create_date%TYPE,
	create_user_in IN CASE_COST.create_user%TYPE);

/********************************************
** DELETE PROCEDURE FOR TABLE "LEGAL.CASE_COST"
** DATE: 02-DEC-03 16:12:45
*********************************************/
PROCEDURE case_cost_delete
(
	case_cost_seq_num_in IN CASE_COST.case_cost_seq_num%TYPE
);

Function DeleteLegalCase(LegalCaseSeqNum IN
LEGAL_CASE.legal_case_Seq_num%TYPE)
    RETURN NUMBER;


PROCEDURE GetCaseNote(CaseId IN  NUMBER,
                      recordset OUT ref_cursor);

/********************************************
** INSERT PROCEDURE FOR TABLE "LEGAL.CASE_NOTE"
** DATE: 06-AUG-03 10:20:33
*********************************************/
FUNCTION case_note_insert
(
	noteId          IN CASE_NOTE.case_note_seq_num%TYPE,
	caseId          IN CASE_NOTE.legal_case_seq_num%TYPE,
    categoryCode    IN CASE_NOTE.NOTE_CATEGORY_CODE%TYPE,
    Notetext        IN CASE_NOTE.text%TYPE,
	createDate      IN CASE_NOTE.create_date%TYPE,
	createUser      IN CASE_NOTE.create_user%TYPE)
   RETURN NUMBER;

/********************************************
** UPDATE PROCEDURE FOR TABLE "LEGAL.CASE_NOTE"
** DATE: 06-AUG-03 10:21:50
*********************************************/
Function case_note_update
(
	noteId       IN CASE_NOTE.case_note_seq_num%TYPE,
	caseId       IN CASE_NOTE.legal_case_seq_num%TYPE,
    categoryCode IN CASE_NOTE.NOTE_CATEGORY_CODE%TYPE,
    NoteText     IN CASE_NOTE.text%TYPE,
	updateDate   IN CASE_NOTE.update_date%TYPE,
	updateUser   IN CASE_NOTE.update_user%TYPE)
    RETURN NUMBER;

/********************************************
** DELETE PROCEDURE FOR TABLE "LEGAL.CASE_NOTE"
** DATE: 07-AUG-03 10:21:48
*********************************************/
Function case_note_delete
(
	noteId CASE_NOTE.case_note_seq_num%TYPE)
    RETURN NUMBER;

PROCEDURE GetSAIFAtty(aLegalCaseNum IN  NUMBER,
                      recordset OUT ref_cursor);

PROCEDURE GetCaseOutcome(LegalCaseID IN  NUMBER,
                      recordset OUT ref_Cursor);

TYPE outcomeIssue_rec IS RECORD
( outcome_issue_seq_num OUTCOME_ISSUE.outcome_issue_seq_num%TYPE,
  case_outcome_seq_num  OUTCOME_ISSUE.case_outcome_seq_num%TYPE,
  OUTCOME_ISSUE_CODE    OUTCOME_ISSUE.OUTCOME_ISSUE_CODE%TYPE,
  win_loss_flag         OUTCOME_ISSUE.win_loss_flag%TYPE,
  fee_amount            OUTCOME_ISSUE.fee_amount%TYPE,
  penalty_flag          NUMBER,
  primary_issue_flag    NUMBER,
  create_date           OUTCOME_ISSUE.create_date%TYPE,
  create_user           OUTCOME_ISSUE.create_user%TYPE,
  summary_text          OUTCOME_ISSUE.summary_text%TYPE,
  OUTCOME_GROUNDS_CODE  OUTCOME_ISSUE.OUTCOME_GROUNDS_CODE%TYPE,
  LEGAL_BODY_PART_CODE  OUTCOME_ISSUE.LEGAL_BODY_PART_CODE%TYPE,
  NATURE_CODE           OUTCOME_ISSUE.NATURE_CODE%TYPE,
  update_date           OUTCOME_ISSUE.update_date%TYPE,
  update_user           OUTCOME_ISSUE.update_user%TYPE
);
TYPE OutcomeIssueCursor IS REF CURSOR RETURN outcomeIssue_rec;
PROCEDURE GetOutcomeIssue(aLegalCaseNum IN  NUMBER,
                      recordset OUT OutcomeIssueCursor);

TYPE ALJList_rec IS RECORD
(  legal_person_seq_num LEGAL_PERSON.legal_person_seq_num%TYPE,
   last_name            LEGAL_PERSON.last_name%TYPE,
   LEGAL_PERSON_TYPE_CODE LEGAL_PERSON_TYPE.LEGAL_PERSON_TYPE_CODE%TYPE,
   code_descr             VARCHAR2(65),
   Selected               NUMBER );
TYPE ALJListCursor IS REF CURSOR RETURN ALJList_rec;

PROCEDURE getAljList(aLegalCaseNum IN  NUMBER,
                      recordset OUT ALJListCursor);

TYPE OutcomeStaff_rec IS RECORD
( outcome_staff_seq_num OUTCOME_STAFF.outcome_staff_seq_num%TYPE,
  case_outcome_seq_num  OUTCOME_STAFF.case_outcome_seq_num%TYPE,
  outcome_staff_person_seq_num OUTCOME_STAFF.outcome_staff_person_seq_num%TYPE,
  last_name  LEGAL_PERSON.last_name%TYPE
);
TYPE OutcomeStaffCursor IS REF CURSOR RETURN OutcomeStaff_rec;
PROCEDURE GetOutcomeStaff(aLegalCaseNum IN  NUMBER,
                      recordset OUT OutcomeStaffCursor);

Function updateCaseOutcome( CaseOutcomeID IN CASE_OUTCOME.case_outcome_seq_num%TYPE,
                             LegalCaseID IN CASE_OUTCOME.legal_case_seq_num%TYPE,
                             OrderTypeCode IN CASE_OUTCOME.ORDER_TYPE_CODE%TYPE,
                             OrderSignedDate IN CASE_OUTCOME.order_signed_date%TYPE := NULL,
                             OrderFiledDate IN CASE_OUTCOME.order_filed_date%TYPE := NULL,
                             UpdateUser IN CASE_OUTCOME.update_user%TYPE := NULL,
                             AsgnAttyEmplid CASE_OUTCOME.asgn_atty_emplid%TYPE := NULL,
                             AppealId CASE_OUTCOME.APPEAL_SEQ_NUM%TYPE := Null)
                             return number;

 Function insertCaseOutcome( CaseOutcomeID IN CASE_OUTCOME.case_outcome_seq_num%TYPE,
                             LegalCaseID IN CASE_OUTCOME.legal_case_seq_num%TYPE,
                             OrderTypeCode IN CASE_OUTCOME.ORDER_TYPE_CODE%TYPE,
                             OrderSignedDate IN CASE_OUTCOME.order_signed_date%TYPE := NULL,
                             OrderFiledDate IN CASE_OUTCOME.order_filed_date%TYPE := NULL,
                             UpdateUser IN CASE_OUTCOME.update_user%TYPE := NULL,
                             AsgnAttyEmplid CASE_OUTCOME.asgn_atty_emplid%TYPE := NULL,
                             AppealId CASE_OUTCOME.APPEAL_SEQ_NUM%TYPE := Null)
                             return number;

PROCEDURE updateOutcomeIssue(outcome_issue_seq_num_in IN OUTCOME_ISSUE.outcome_issue_seq_num%TYPE,
	case_outcome_seq_num_in IN OUTCOME_ISSUE.case_outcome_seq_num%TYPE,
	outcome_issue_code_in IN OUTCOME_ISSUE.OUTCOME_ISSUE_CODE%TYPE,
	win_loss_flag_in IN OUTCOME_ISSUE.win_loss_flag%TYPE,
	fee_amount_in IN OUTCOME_ISSUE.fee_amount%TYPE,
	penalty_flag_in IN OUTCOME_ISSUE.penalty_flag%TYPE,
	primary_issue_flag_in IN OUTCOME_ISSUE.primary_issue_flag%TYPE,
    summary_text_in IN OUTCOME_ISSUE.summary_text%TYPE,
    outcome_grounds_code_in IN OUTCOME_ISSUE.OUTCOME_GROUNDS_CODE%TYPE,
	body_part_code_in IN OUTCOME_ISSUE.LEGAL_BODY_PART_CODE%TYPE,
	nature_code_in IN OUTCOME_ISSUE.NATURE_CODE%TYPE,
    userid_in IN OUTCOME_ISSUE.update_user%TYPE);

PROCEDURE updateOutcomeStaff( outcome_staff_seq_num_in IN OUTCOME_STAFF.outcome_staff_seq_num%TYPE,
	                          case_outcome_seq_num_in IN OUTCOME_STAFF.case_outcome_seq_num%TYPE,
	                          staff_person_seq_num_in IN OUTCOME_STAFF.outcome_staff_person_seq_num%TYPE );

FUNCTION DeleteCaseOutcome(case_outcome_seq_num_in IN
                            CASE_OUTCOME.case_outcome_seq_num%TYPE) RETURN NUMBER;

PROCEDURE DeleteOutcomeIssue(outcome_issue_seq_num_in IN
                             OUTCOME_ISSUE.outcome_issue_seq_num%TYPE);

PROCEDURE DeleteOutcomeStaff(outcome_staff_seq_num_in IN
                             OUTCOME_STAFF.outcome_staff_seq_num%TYPE);

PROCEDURE SetFinalOutcome( legal_case_seq_num_in IN
                           LEGAL_CASE.legal_case_seq_num%TYPE);

TYPE LegalCaseEmployer_rec IS RECORD
( legal_case_employer_seq_num   NUMBER,
  legal_case_seq_num            NUMBER,
  EMPLOYER_OTHER_FIRM_SEQ_NUM   NUMBER,
  ATTORNEY_OTHER_FIRM_SEQ_NUM   NUMBER,
  ATTORNEY_LEGAL_PERSON_SEQ_NUM NUMBER,
  EMPLOYER_DISCOVERY_FLAG       VARCHAR2(1),
  ATTORNEY_DISCOVERY_FLAG       VARCHAR2(1));

TYPE LegalCaseEmployerCursor IS REF CURSOR RETURN LegalCaseEmployer_rec;

PROCEDURE GetLegalCaseEmployer(aLegalCaseNum IN  NUMBER,
                      recordset OUT LegalCaseEmployerCursor);

/********************************************
** INSERT PROCEDURE FOR TABLE "LEGAL.LEGAL_CASE_EMPLOYER"
** DATE: 03-SEP-03 08:41:55
*********************************************/
PROCEDURE legal_case_employer_insert
(
	legal_case_employer_seq_num_in IN LEGAL_CASE_EMPLOYER.legal_case_employer_seq_num%TYPE,
	legal_case_seq_num_in IN LEGAL_CASE_EMPLOYER.legal_case_seq_num%TYPE,
	other_firm_seq_num_in IN LEGAL_CASE_EMPLOYER.employer_other_firm_seq_num%TYPE,
	discovery_flag_in IN LEGAL_CASE_EMPLOYER.employer_discovery_flag%TYPE,
	attorney_discovery_flag_in IN LEGAL_CASE_EMPLOYER.attorney_discovery_flag%TYPE,
	atty_other_firm_seq_num_in IN LEGAL_CASE_EMPLOYER.attorney_other_firm_seq_num%TYPE,
	atty_legal_person_seq_num_in IN LEGAL_CASE_EMPLOYER.attorney_legal_person_seq_num%TYPE );
/********************************************
** UPDATE PROCEDURE FOR TABLE "LEGAL.LEGAL_CASE_EMPLOYER"
** DATE: 03-SEP-03 08:43:30
*********************************************/
PROCEDURE legal_case_employer_update
(
	legal_case_employer_seq_num_in IN LEGAL_CASE_EMPLOYER.legal_case_employer_seq_num%TYPE,
	legal_case_seq_num_in IN LEGAL_CASE_EMPLOYER.legal_case_seq_num%TYPE,
	other_firm_seq_num_in IN LEGAL_CASE_EMPLOYER.employer_other_firm_seq_num%TYPE,
	discovery_flag_in IN LEGAL_CASE_EMPLOYER.employer_discovery_flag%TYPE,
	attorney_discovery_flag_in IN LEGAL_CASE_EMPLOYER.attorney_discovery_flag%TYPE,
	atty_other_firm_seq_num_in IN LEGAL_CASE_EMPLOYER.attorney_other_firm_seq_num%TYPE,
	atty_legal_person_seq_num_in IN LEGAL_CASE_EMPLOYER.attorney_legal_person_seq_num%TYPE
);

PROCEDURE GetWorkerAtty(recordset OUT ref_cursor, inFirmLocID IN NUMBER, inAttyLocID IN NUMBER);

TYPE AttyLeave_rec IS RECORD
(schedule_emplid ATTORNEY_SCHEDULE.schedule_emplid%TYPE,
 atty_name       VARCHAR2(80),
 vac_days        NUMBER,
 med_days		   NUMBER);
TYPE AttyLeaveCursor IS REF CURSOR RETURN AttyLeave_rec;

PROCEDURE GetAttyLeaveReport(inStartDate IN DATE,
                             inStopDate IN DATE,
                             recordset OUT AttyLeaveCursor);

PROCEDURE alertlegalcase(inClaimId IN NUMBER, OutAlertFlag OUT VARCHAR2 ) ;

PROCEDURE SelectImageAlertsHist(inCaseSeqNum IN NUMBER, recordset OUT ref_cursor);

PROCEDURE SelectLegalEmployees(recordset OUT ref_cursor);

PROCEDURE getPolicyAddresses(inPolicyNum IN NUMBER,
       recordset OUT ref_cursor);
END;
```

</details>

<details>
<summary>PL/SQL package/procedure body</summary>

```sql
PACKAGE BODY                   Pk80001_Legalnet AS

/*****************************************************************
*Package Title  :
*Name           : pk80001_legalnet
*****************************************************************
*Description:
*   find a legal case based on claim, wcb or dcbs number.
*
*PROGRAMMER     ANALYST                 DATE       MOD #
* Melhil                             03/11/2010     Remove legacy log, get supervisor from ADP not claim_supervisor table
* Tony Busch                        07/27/2005     Change PROVIDER_LOCATION_ID to PROVIDER_LOCATION_SEQ_NUM.
* Melhil                            01/06/2008     Add amount per hour to case cost
*******************************************************************/

  PROCEDURE findCase(recordset OUT ref_cursor, in_num IN VARCHAR2)
    AS
       v_count NUMBER;
    BEGIN
       SELECT COUNT(*) INTO v_count
       FROM LEGAL_CASE
       WHERE wcb_num = in_num;
    IF v_count > 0 THEN
       OPEN recordset FOR
        SELECT
        DISTINCT l.legal_case_seq_num LegalCaseSeqNum,
        l.wcb_num as WcbNum,
        l.claim_id claimId,
         l.dcbs_num dcbsNum,
           l.owcp_num  owcpNum,
       l.oalj_num  oaljNum,
       brb_num brbNum,
       s.code_descr AS caseStatus,
       t.code_descr AS caseTypeCode
       FROM LEGAL_CASE l,
            CASE_STATUS_CODE s, CASE_TYPE_CODE t
       WHERE l.wcb_num = in_num
         AND l.CASE_STATUS_CODE = s.code
         and L.CASE_TYPE_CODE = T.CODE;
  ELSE
    IF LENGTH(in_num) <= 7 THEN
       SELECT COUNT(*) INTO v_count
       FROM LEGAL_CASE
       WHERE dcbs_num = in_num;
       IF v_count > 0 THEN
        OPEN recordset FOR
        SELECT
           DISTINCT  l.legal_case_seq_num LegalCaseSeqNum,
        l.wcb_num as WcbNum,
        l.claim_id claimId,
         l.dcbs_num dcbsNum,
           l.owcp_num  owcpNum,
       l.oalj_num  oaljNum,
       brb_num brbNum,
       s.code_descr AS caseStatus,
       t.code_descr AS caseTypeCode
        FROM LEGAL_CASE l,
            CASE_STATUS_CODE s, CASE_TYPE_CODE t
        WHERE l.dcbs_num = in_num
          AND l.CASE_STATUS_CODE = s.code
           and L.CASE_TYPE_CODE = T.CODE;
   ELSE
      SELECT COUNT(*) INTO v_count
       FROM LEGAL_CASE
       WHERE owcp_num = in_num;
       IF v_count > 0 THEN
          OPEN recordset FOR
        SELECT
           DISTINCT  l.legal_case_seq_num LegalCaseSeqNum,
        l.wcb_num as WcbNum,
        l.claim_id claimId,
         l.dcbs_num dcbsNum,
           l.owcp_num  owcpNum,
       l.oalj_num  oaljNum,
       brb_num brbNum,
       s.code_descr AS caseStatus,
       t.code_descr AS caseTypeCode
        FROM LEGAL_CASE l,
            CASE_STATUS_CODE s, CASE_TYPE_CODE t
        WHERE (l.owcp_num = in_num)
          AND l.CASE_STATUS_CODE = s.code
           and L.CASE_TYPE_CODE = T.CODE;
    ELSE
       SELECT COUNT(*) INTO v_count
       FROM LEGAL_CASE
       WHERE oalj_num = in_num;
       IF v_count > 0 THEN
        OPEN recordset FOR
        SELECT
           DISTINCT l.legal_case_seq_num LegalCaseSeqNum,
        l.wcb_num as WcbNum,
        l.claim_id claimId,
         l.dcbs_num dcbsNum,
           l.owcp_num  owcpNum,
       l.oalj_num  oaljNum,
       brb_num brbNum,
       s.code_descr AS caseStatus,
       t.code_descr AS caseTypeCode
        FROM LEGAL_CASE l,
            CASE_STATUS_CODE s, CASE_TYPE_CODE t
       WHERE oalj_num = in_num
         AND l.CASE_STATUS_CODE = s.code
          and L.CASE_TYPE_CODE = T.CODE;
    ELSE
      SELECT COUNT(*) INTO v_count
       FROM LEGAL_CASE
       WHERE brb_num = in_num;
    IF v_count > 0 THEN
        OPEN recordset FOR
        SELECT
           DISTINCT  l.legal_case_seq_num LegalCaseSeqNum,
        l.wcb_num as WcbNum,
        l.claim_id claimId,
         l.dcbs_num dcbsNum,
           l.owcp_num  owcpNum,
       l.oalj_num  oaljNum,
       brb_num brbNum,
       s.code_descr AS caseStatus,
       t.code_descr AS caseTypeCode
        FROM LEGAL_CASE l,
            CASE_STATUS_CODE s, CASE_TYPE_CODE t
       WHERE l.brb_num = in_num
          AND l.CASE_STATUS_CODE = s.code
           and L.CASE_TYPE_CODE = T.CODE;
    ELSE
        OPEN recordset FOR
        SELECT
           DISTINCT  l.legal_case_seq_num LegalCaseSeqNum,
        l.wcb_num as WcbNum,
        l.claim_id claimId,
         l.dcbs_num dcbsNum,
           l.owcp_num  owcpNum,
       l.oalj_num  oaljNum,
       brb_num brbNum,
       s.code_descr AS caseStatus,
       t.code_descr AS caseTypeCode
        FROM LEGAL_CASE l,
             CASE_STATUS_CODE s, APPEAL A, CASE_TYPE_CODE t
       WHERE A.appeal_num = in_num
          AND l.CASE_STATUS_CODE = s.code
          AND l.legal_case_seq_num = A.legal_case_seq_num
           and L.CASE_TYPE_CODE = T.CODE;
       END IF;
       END IF;
       END IF;
       END IF;
    END IF;
  END IF;

 END findcase;

FUNCTION get_hearing_date (in_case_seq IN LEGAL_CASE.legal_case_seq_num%TYPE)
  RETURN DATE AS
         v_hear_date DATE;
BEGIN
BEGIN
   SELECT MAX(sch.schedule_date)
     INTO v_hear_date
     FROM LEGAL_CASE cas,
          SCHEDULE sch,
          CONSOLIDATED_CASE c
   WHERE cas.LEGAL_CASE_SEQ_NUM = in_case_seq
    AND sch.schedule_seq_num =
             (SELECT MAX(schedule_seq_num)
              FROM SCHEDULE sch2, CONSOLIDATED_CASE c2
             WHERE ((sch2.LEGAL_CASE_SEQ_NUM = cas.LEGAL_CASE_SEQ_NUM)
                OR (c2.secondary_case_seq_num = in_case_seq
               AND sch2.legal_case_seq_num = c2.primary_case_seq_num))
               AND sch2.SCHEDULE_TYPE_CODE = 'HEAR');
EXCEPTION
    WHEN NO_DATA_FOUND THEN
       NULL;
   END;

 RETURN v_hear_Date;
END get_hearing_date;

FUNCTION get_consolidated_hearing_seq (in_case_seq IN LEGAL_CASE.legal_case_seq_num%TYPE)
   RETURN NUMBER AS
   v_hear_seq NUMBER(15,0);
 BEGIN
   BEGIN
       SELECT cas.most_recent_hearing_seq_num
         INTO v_hear_seq
       FROM LEGAL_CASE cas,
            CONSOLIDATED_CASE con
      WHERE con.secondary_case_seq_num = in_case_seq
        AND con.primary_case_seq_num = cas.legal_case_seq_num;
        EXCEPTION
    WHEN NO_DATA_FOUND THEN
         NULL;
    END;
    RETURN v_hear_seq;

END get_consolidated_hearing_seq;


FUNCTION get_hearingID(in_case_seq IN LEGAL_CASE.legal_case_seq_num%TYPE)
  RETURN NUMBER AS
    v_hear_seq NUMBER(15,0);
BEGIN
BEGIN
   SELECT MAX(sch.SCHEDULE_SEQ_NUM)
     INTO v_hear_seq
     FROM LEGAL_CASE cas,
          SCHEDULE sch
    WHERE cas.LEGAL_CASE_SEQ_NUM = in_case_seq
      AND sch.schedule_seq_num =
             (SELECT MAX(schedule_seq_num)
              FROM SCHEDULE sch2, CONSOLIDATED_CASE c2
             WHERE ((sch2.LEGAL_CASE_SEQ_NUM = cas.LEGAL_CASE_SEQ_NUM)
                OR (c2.secondary_case_seq_num = in_case_seq
               AND sch2.legal_case_seq_num = c2.primary_case_seq_num))
               AND sch2.SCHEDULE_TYPE_CODE = 'HEAR');
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;
IF v_hear_seq IS NULL THEN
   BEGIN
    SELECT MAX(sch.SCHEDULE_SEQ_NUM)
     INTO v_hear_seq
     FROM LEGAL_CASE cas,
          SCHEDULE sch
   WHERE cas.LEGAL_CASE_SEQ_NUM = in_case_seq
     AND sch.LEGAL_CASE_SEQ_NUM = CAS.LEGAL_CASE_SEQ_NUM
     AND sch.SCHEDULE_TYPE_CODE = 'HEAR';
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
          NULL;
    END;
 END IF;

 RETURN v_hear_seq;
END get_hearingID;



  FUNCTION get_asgn_atty(IN_case_Seq_num IN LEGAL_CASE.legal_case_seq_num%TYPE)
         RETURN VARCHAR2 AS
         v_atty VARCHAR2(50);
   BEGIN
    BEGIN
    SELECT LAST_NAME || ', ' || DECODE(preferred_name, ' ', FIRST_NAME,preferred_name)
      INTO v_atty
         FROM LEGAL_CASE cas,
               APPEAL app,
               adp_employee emp
         WHERE cas.legal_case_seq_num = in_case_Seq_num
            AND app.legal_case_seq_num = cas.legal_case_seq_num
            AND app.appeal_seq_num =
                (SELECT MAX(appeal_seq_num)
                   FROM APPEAL a2
                  WHERE a2.legal_case_Seq_num = cas.legal_case_seq_num)
             AND emp.emplid = app.atty_emplid;
     EXCEPTION
      WHEN NO_DATA_FOUND THEN
      BEGIN
    SELECT LAST_NAME || ', ' || DECODE(preferred_name, ' ', FIRST_NAME,preferred_name)
          INTO v_atty
          FROM LEGAL_CASE cas,
               SCHEDULE sch,
               adp_employee emp
         WHERE cas.legal_case_seq_num = in_case_Seq_num
           AND emp.emplid = sch.asgn_attorney_emplid
           AND cas.most_recent_hearing_seq_num = sch.schedule_seq_num;
  EXCEPTION
      WHEN NO_DATA_FOUND THEN
      BEGIN
      SELECT FIRM_NAME INTO v_atty
        FROM LEGAL_CASE cas,
               SCHEDULE sch,
               OTHER_FIRM frm
         WHERE cas.legal_case_seq_num = in_case_Seq_num
           AND frm.other_firm_seq_num = sch.asgn_other_firm_Seq_num
            AND cas.most_recent_hearing_seq_num = sch.schedule_seq_num;

      EXCEPTION
          WHEN NO_DATA_FOUND THEN
               v_atty := '';
      END;
     END;
    END;
  RETURN v_atty;
END get_asgn_atty;

FUNCTION get_asgn_atty_id(IN_case_Seq_num IN
LEGAL_CASE.legal_case_seq_num%TYPE) RETURN VARCHAR2 AS
         v_atty_id ATTORNEY_PROFILE.profile_emplid%TYPE;
   BEGIN
    BEGIN
    SELECT emp.emplid INTO v_atty_id
         FROM LEGAL_CASE cas,
               APPEAL app,
               adp_employee emp
         WHERE cas.legal_case_seq_num = in_case_Seq_num
            AND app.legal_case_seq_num = cas.legal_case_seq_num
            AND app.appeal_seq_num =
                (SELECT MAX(appeal_seq_num)
                   FROM APPEAL a2
                  WHERE a2.legal_case_Seq_num = cas.legal_case_seq_num)
             AND emp.emplid = app.atty_emplid;
     EXCEPTION
      WHEN NO_DATA_FOUND THEN
      BEGIN
    SELECT emp.emplid INTO v_atty_id
          FROM LEGAL_CASE cas,
               SCHEDULE sch,
               adp_employee emp
         WHERE cas.legal_case_seq_num = in_case_Seq_num
           AND emp.emplid = sch.asgn_attorney_emplid
          AND cas.most_recent_hearing_seq_num = sch.schedule_seq_num;

      EXCEPTION
      WHEN NO_DATA_FOUND THEN
      BEGIN
      SELECT 'OUTSIDECNCL' INTO v_atty_ID
        FROM LEGAL_CASE cas,
               SCHEDULE sch
        WHERE cas.legal_case_seq_num = in_case_Seq_num
         AND sch.asgn_other_firm_Seq_num IS NOT NULL
         AND cas.most_recent_hearing_seq_num = sch.schedule_seq_num;
      EXCEPTION
          WHEN NO_DATA_FOUND THEN
                          v_atty_id := '';
      END;
     END;
    END;
  RETURN v_atty_id;
END get_asgn_atty_id;

FUNCTION get_atty_mngr_email(aLegalCaseNum IN NUMBER)
 RETURN VARCHAR2 AS
       v_email VARCHAR2(50);
   BEGIN
    BEGIN
        SELECT EMAIL_ADDRESS INTO v_email
          FROM LEGAL_CASE cas,
               SCHEDULE sch,
               adp_employee emp,
               ATTORNEY_PROFILE P
         WHERE cas.legal_case_seq_num = aLegalCaseNum
           AND cas.most_recent_hearing_seq_num = sch.schedule_seq_num
           AND P.profile_emplid = sch.asgn_attorney_emplid
           AND P.manager_emplid = emp.emplid;
  EXCEPTION
      WHEN NO_DATA_FOUND THEN
         BEGIN
        SELECT EMAIL_ADDRESS INTO v_email
          FROM LEGAL_CASE cas,
               SCHEDULE sch,
               adp_employee emp,
               ATTORNEY_PROFILE P
         WHERE cas.legal_case_seq_num = aLegalCaseNum
           AND cas.most_recent_hearing_seq_num = sch.schedule_seq_num
           AND P.profile_emplid = 'OUTSIDECNCL'
           AND sch.asgn_other_firm_seq_num IS NOT NULL
           AND P.manager_emplid = emp.emplid;
     EXCEPTION
        WHEN NO_DATA_FOUND THEN
          v_email := 'NONE';
     END;
  END;
RETURN v_email;

 END get_atty_mngr_email;


FUNCTION get_mailid_for_emplid(inEmplid IN VARCHAR2) RETURN VARCHAR2
AS
      v_mailid VARCHAR2(50);
   BEGIN
    BEGIN
        SELECT user_id INTO v_mailid
          FROM adp_employee
         WHERE emplid = inEmplid;
  EXCEPTION
      WHEN NO_DATA_FOUND THEN
          v_mailid := '';
  END;
  RETURN v_mailid;
END get_mailid_for_emplid;

 FUNCTION get_case_num(aLegalCaseNum IN NUMBER)
 RETURN VARCHAR2 AS
       v_num VARCHAR2(15) := ' ';
   BEGIN
    BEGIN
    SELECT wcb_num INTO v_num
      FROM LEGAL_CASE
     WHERE legal_case_seq_num = aLegalCaseNum
       AND wcb_num IS NOT NULL
 UNION
    SELECT dcbs_num
      FROM LEGAL_CASE
     WHERE legal_case_seq_num = aLegalCaseNum
       AND wcb_num IS NULL
       AND dcbs_num IS NOT NULL
 UNION
    SELECT owcp_num
      FROM LEGAL_CASE
     WHERE legal_case_seq_num = aLegalCaseNum
       AND wcb_num IS NULL
       AND dcbs_num IS NULL
       AND owcp_num IS NOT NULL
 UNION
    SELECT oalj_num
      FROM LEGAL_CASE
     WHERE legal_case_seq_num = aLegalCaseNum
       AND wcb_num IS NULL
       AND dcbs_num IS NULL
       AND owcp_num IS NULL
       AND oalj_num IS NOT NULL
 UNION
    SELECT brb_num
      FROM LEGAL_CASE
     WHERE legal_case_seq_num = aLegalCaseNum
       AND wcb_num IS NULL
       AND dcbs_num IS NULL
       AND owcp_num IS NULL
       AND oalj_num IS NULL
       AND brb_num IS NOT NULL;
  EXCEPTION
      WHEN NO_DATA_FOUND THEN
          v_num := ' ';
  END;
  RETURN v_num;

 END get_case_num;

FUNCTION get_case_issue_codes (in_case_seq_num
NUMBER) RETURN VARCHAR2 AS
  v_dummy VARCHAR2(100) := '';
  CURSOR get_issues_cur IS
      SELECT FORM_REQUEST_CODE
        FROM REQUEST_ISSUE_CODE C, REQUEST_ISSUE I
       WHERE I.REQUEST_ISSUE_CODE = c.code
         AND I.legal_case_seq_num = in_case_seq_num
      ORDER BY FORM_REQUEST_CODE;
  issue_rec  get_issues_cur%ROWTYPE;
BEGIN
    OPEN get_issues_cur;
    FETCH get_issues_cur INTO  issue_rec;

    WHILE get_issues_cur%FOUND
    LOOP
        v_dummy := v_dummy || issue_rec.form_request_code || ' ';
        FETCH get_issues_cur INTO  issue_rec;
    END LOOP;

    CLOSE get_issues_cur;
    RETURN v_dummy;
END;

FUNCTION get_case_issue_text (in_case_seq_num
NUMBER) RETURN VARCHAR2 AS
  v_dummy VARCHAR2(200) := '';
  CURSOR get_issues_cur IS
      SELECT trim(SHORT_DESCR) AS code_descr
        FROM REQUEST_ISSUE_CODE C, REQUEST_ISSUE I
       WHERE I.REQUEST_ISSUE_CODE = c.code
         AND I.legal_case_seq_num = in_case_seq_num
      ORDER BY CODE_DESCR;
  issue_rec  get_issues_cur%ROWTYPE;
BEGIN
    OPEN get_issues_cur;
    FETCH get_issues_cur INTO  issue_rec;

    WHILE get_issues_cur%FOUND
    LOOP
        v_dummy := v_dummy || issue_rec.code_descr || ' ';
        FETCH get_issues_cur INTO  issue_rec;
    END LOOP;

    CLOSE get_issues_cur;
    RETURN v_dummy;
END;


PROCEDURE Casesearch(recordset OUT search_cursor,
                        claimIdsCommaDelimited IN VARCHAR2,
                        includeClosed IN VARCHAR2) AS
       v_status_code VARCHAR2(5);
       V_EMPR_NUM NUMBER;

    BEGIN
       IF includeClosed  = 'Y' THEN
          v_status_code := 'NONE';
       ELSE
          v_status_code := 'CLOSE';
       END IF;

    OPEN recordset FOR
       SELECT /*+ FIRST_ROWS */
        get_case_num(cas.legal_case_seq_num) AS wcb_num,
        sts.code_descr AS case_status,
        get_asgn_atty(cas.Legal_Case_seq_Num) AS atty_name,
        TO_CHAR(schedule_date,'MM/DD/YYYY') AS hearing_date,
        get_case_issue_codes(cas.legal_case_seq_num) AS REQUEST_ISSUE,
        req.code_descr AS requested_by,
        cas.legal_case_seq_num,
        CAS.claim_id
       FROM LEGAL_CASE cas,
            SCHEDULE s,
            CASE_STATUS_CODE sts,
            REQUESTED_BY_CODE req
     WHERE
       cas.claim_id IN (
               -- https://blogs.oracle.com/sql/post/split-comma-separated-values-into-rows-in-oracle-database
               with claimsString as (
                  select claimIdsCommaDelimited str from dual
               )
               select regexp_substr (claimIdsCommaDelimited, '[^,]+', 1, level) value
               from   claimsString
               connect by level <=
               length ( claimIdsCommaDelimited ) - length ( replace ( claimIdsCommaDelimited, ',' ) ) + 1
            )
       AND cas.most_recent_hearing_seq_num = s.schedule_seq_num (+)
       AND cas.CASE_STATUS_CODE = sts.code
       AND cas.REQUESTED_BY_CODE = req.code
       AND cas.CASE_STATUS_CODE <> v_status_code
       ORDER BY 1;
END Casesearch;

 PROCEDURE updateCaseStatus(aLegalCaseNum IN NUMBER, newStatus IN VARCHAR2,
            updateDate IN DATE, updateUser IN VARCHAR2)
        AS BEGIN
        UPDATE LEGAL_CASE
           SET CASE_STATUS_CODE = newStatus,
               update_date = updateDate,
               update_user = updateUser
         WHERE legal_case_seq_num = aLegalCaseNum;

          -- Update consolidated Cases too  ---
        UPDATE LEGAL_CASE
           SET CASE_STATUS_CODE = newStatus,
                   update_date = updateDate,
                   update_user = updateUser
         WHERE legal_case_seq_num IN
             (SELECT secondary_case_seq_num
                FROM CONSOLIDATED_CASE
               WHERE primary_case_seq_num =  aLegalCaseNum);

 END updateCaseStatus;

 PROCEDURE updateHearingSeqNum(aLegalCaseNum IN NUMBER)
  AS          v_hear_seq NUMBER(15,0);
   BEGIN
   BEGIN
    SELECT MAX(schedule_seq_num)
      INTO v_hear_seq
      FROM SCHEDULE s, CONSOLIDATED_CASE c
             WHERE c.secondary_case_seq_num = aLegalCaseNum
               AND s.legal_case_seq_num = c.primary_case_seq_num
               AND s.SCHEDULE_TYPE_CODE = 'HEAR';
  EXCEPTION
       WHEN NO_DATA_FOUND THEN
       NULL;
  END;
  IF v_hear_seq IS NULL THEN
      BEGIN
     SELECT MAX(schedule_seq_num)
       INTO v_hear_seq
       FROM SCHEDULE s
      WHERE s.legal_case_seq_num = aLegalCaseNum
        AND s.SCHEDULE_TYPE_CODE = 'HEAR';
     EXCEPTION
          WHEN NO_DATA_FOUND THEN
          NULL;
      END;
  END IF;
   IF v_hear_seq IS NULL THEN
      BEGIN
     SELECT MAX(schedule_seq_num)
       INTO v_hear_seq
       FROM SCHEDULE s
      WHERE s.legal_case_seq_num = aLegalCaseNum
        AND (s.SCHEDULE_TYPE_CODE = 'CCALL'
        OR s.SCHEDULE_TYPE_CODE = 'INCON'
        OR s.SCHEDULE_TYPE_CODE = 'MED');
     EXCEPTION
          WHEN NO_DATA_FOUND THEN
          NULL;
      END;
  END IF;


  UPDATE LEGAL_CASE
     SET most_recent_hearing_seq_num = v_hear_seq
    WHERE legal_case_seq_num = aLegalCaseNum;

  -- Update consolidated Cases too  ---
  UPDATE LEGAL_CASE
     SET most_recent_hearing_seq_num = v_hear_seq
   WHERE legal_case_seq_num IN
         (SELECT secondary_case_seq_num
            FROM CONSOLIDATED_CASE
           WHERE primary_case_seq_num =  aLegalCaseNum);


 END updateHearingSeqNum;

  PROCEDURE getLCE(LegalCaseID IN NUMBER,
       Recordset OUT ref_cursor)
    AS BEGIN OPEN recordset FOR
       SELECT LEGAL_CASE_EVALUATION_SEQ_NUM EvaluationID,
       LEGAL_CASE_SEQ_NUM LegalCaseID,
       LCE_EXCUSED_FLAG IsExcused,
       LCE_DUE_DATE DueDate, LCE_FILED_DATE FiledDate,
       ADJUSTER_EMPLID AdjusterEmployeeID, CASE_TRANSFER_DATE CaseTransferDate,
       CREATE_DATE CreateDate, CREATE_USER CreateUser,
       UPDATE_DATE UpdateDate, UPDATE_USER UpdateUser,
       ASGN_ATTY_EMPLID AsgnAttyEmplid
       FROM LEGAL_CASE_EVALUATION
       WHERE LEGAL_CASE_SEQ_NUM = LegalCaseID
       ORDER BY legal_case_evaluation_seq_num DESC;

    END getLCE;

    Function updateLCE(EvaluationID IN NUMBER, IsExcused IN VARCHAR2, dueDate IN DATE,
              filedDate IN DATE, AdjusterEmployeeID IN VARCHAR2, casetransferDate IN DATE,
              updateDate IN DATE, updateUser IN VARCHAR2,
              AsgnAttyEmplid IN VARCHAR2) return number
       AS BEGIN

       UPDATE LEGAL_CASE_EVALUATION
                 SET lce_excused_flag = IsExcused,
                     lce_due_date = dueDate,
                     lce_filed_date = filedDate,
                     adjuster_emplid = AdjusterEmployeeID,
                     case_transfer_date = caseTransferDate,
                     update_date = updateDate,
                     update_user = updateUser,
                     asgn_atty_emplid = AsgnAttyEmplid
               WHERE legal_case_evaluation_seq_num = EvaluationID;
               return sql%rowcount;
       END updateLCE;

 Function insertLCE(EvaluationID IN NUMBER, LegalCaseID in number,
              IsExcused IN VARCHAR2, dueDate IN DATE,
              filedDate IN DATE, AdjusterEmployeeID IN VARCHAR2, casetransferDate IN DATE,
              createDate IN DATE, createUser IN VARCHAR2,
              AsgnAttyEmplid IN VARCHAR2)
              return number
       AS BEGIN
           INSERT INTO LEGAL_CASE_EVALUATION
      (legal_case_evaluation_seq_num, legal_case_seq_num,
       lce_excused_flag,    lce_due_date,
       lce_filed_date, adjuster_emplid,
       case_transfer_date, create_date, create_user, asgn_atty_emplid)
    VALUES
      (EvaluationID, LegalCaseID, Isexcused, dueDate, filedDate,
       AdjusterEmployeeID, casetransferDate, createDate, createUser,
       AsgnAttyEmplid);
     return sql%rowcount;
    EXCEPTION
         WHEN OTHERS THEN
           RAISE_APPLICATION_ERROR(-20004,
           'Error in insertLCE: '||SQLERRM);
    END insertLCE;

   PROCEDURE getSchedule(LegalCaseSeqNum IN NUMBER,
       recordset OUT ref_cursor) AS
     BEGIN
      OPEN Recordset FOR
      SELECT schedule_seq_num as ScheduleSeqNum,
               legal_case_seq_num as LegalCaseSeqNum,
               schedule_date as ScheduleDate,
               schedule_time as ScheduleTime,
               alj_person_seq_num as AljPersonSeqNum,
               hearing_location_Seq_num as HearingLocationSeqNum,
               asgn_attorney_emplid as AsgnAttorneyEmplid,
               DURATION_CODE as DurationCode,
               SCHEDULE_TYPE_CODE as ScheduleTypeCode,
               SCHEDULE_STATUS_CODE as ScheduleStatusCode,
               KEEP_ATTORNEY_FLAG as KeepAttorneyFlag,
               assigned_date as AssignedDate,
               reminder_date as ReminderDate,
               schedule_text as ScheduleText,
               create_user as CreateUser,
               create_date as CreateDate,
               update_user as UpdateUser,
               update_date as UpdateDate,
               asgn_other_firm_seq_num as AsgnOtherFirmSeqNum,
               asgn_person_seq_num as AsgnPersonSeqNum
          FROM SCHEDULE
         WHERE legal_case_seq_num = LegalCaseSeqNum
       UNION
       SELECT schedule_seq_num,
               legal_case_seq_num,
               schedule_date,
               schedule_time,
               alj_person_seq_num,
               hearing_location_Seq_num,
               asgn_attorney_emplid,
               DURATION_CODE,
               SCHEDULE_TYPE_CODE,
               SCHEDULE_STATUS_CODE,
               keep_attorney_flag,
               assigned_date,
               reminder_date,
               schedule_text,
               s.create_user,
               s.create_date,
               s.update_user,
               s.update_date,
               asgn_other_firm_seq_num,
               asgn_person_seq_num
          FROM SCHEDULE s, CONSOLIDATED_CASE c
         WHERE c.secondary_case_seq_num = LegalCaseSeqNum
           AND c.primary_case_seq_num = s.legal_case_seq_num
         ORDER BY ScheduleSeqNum;

      END getSchedule;

   Function updateSchedule(ScheduleSeqNum IN NUMBER,
    ScheduleDate IN DATE, ScheduleTime IN VARCHAR2, AljPersonSeqNum IN NUMBER,
    HearingLocationSeqNum IN NUMBER, AsgnAttorneyEmplid IN VARCHAR2, DurationCode IN VARCHAR2,
    ScheduleTypeCode IN VARCHAR2, ScheduleStatusCode IN VARCHAR2, KeepAttorneyFlag IN VARCHAR2,
    AssignedDate IN DATE, ReminderDate IN DATE, ScheduleText IN VARCHAR2,
    UpdateDate IN DATE, UpdateUser IN VARCHAR2,
    AsgnOtherFirmSeqNum IN NUMBER, AsgnPersonSeqNum IN NUMBER)
  RETURN NUMBER
    IS
    v_update_count             NUMBER;
    BEGIN
       UPDATE SCHEDULE
                 SET schedule_date = ScheduleDate,
                     schedule_time = ScheduleTime,
                     alj_person_Seq_num = decode(AljPersonSeqNum, 0, null, AljPersonSeqNum),
                     hearing_location_seq_num = decode(HearingLocationSeqNum, 0, null, HearingLocationSeqNum),
                     asgn_attorney_emplid = AsgnAttorneyEmplid,
                     DURATION_CODE = durationCode,
                     SCHEDULE_TYPE_CODE = ScheduleTypeCode,
                     SCHEDULE_STATUS_CODE = ScheduleStatusCode,
                     keep_attorney_flag = KeepAttorneyFlag,
                     assigned_date = AssignedDate,
                     reminder_date = reminderDate,
                     schedule_text = ScheduleText,
                     update_date = updateDate,
                     update_user = updateUser,
                     asgn_Other_Firm_Seq_Num = asgnOtherFirmSeqNum,
                     asgn_Person_Seq_Num = asgnPersonSeqNum
               WHERE schedule_seq_num = ScheduleSeqNum;
           v_update_count := SQL%Rowcount;
    RETURN v_update_count;
    END updateSchedule;

Function insertSchedule(ScheduleSeqNum IN NUMBER, LegalCaseSeqNum IN NUMBER,
      ScheduleDate IN DATE, ScheduleTime IN VARCHAR2, AljPersonSeqNum IN NUMBER,
      HearingLocationSeqNum IN NUMBER, AsgnAttorneyEmplid IN VARCHAR2, DurationCode IN VARCHAR2,
      ScheduleTypeCode IN VARCHAR2, ScheduleStatusCode IN VARCHAR2, KeepAttorneyFlag IN VARCHAR2,
      AssignedDate IN DATE, ReminderDate IN DATE, ScheduleText IN VARCHAR2,
      createDate IN DATE, createUser IN VARCHAR2,
      asgnOtherFirmSeqNum IN NUMBER, asgnPersonSeqNum IN NUMBER)
  RETURN NUMBER
    IS
    v_update_count             NUMBER;
    BEGIN
         INSERT INTO SCHEDULE
      (schedule_seq_num, legal_case_seq_num,
       schedule_date, schedule_time,
       alj_person_Seq_num, hearing_location_seq_num,
       asgn_attorney_emplid, DURATION_CODE,
       SCHEDULE_TYPE_CODE, SCHEDULE_STATUS_CODE,
       keep_attorney_flag, assigned_date,
       reminder_date, schedule_text, create_date, create_user,
       asgn_Other_Firm_Seq_Num, asgn_Person_Seq_Num,
       update_user, update_date)
    VALUES
      (ScheduleSeqNum, LegalCaseSeqNum,
      ScheduleDate, ScheduleTime, decode(aljPersonSeqNum, 0, null, aljPersonSeqNum),
      decode(HearinglocationSeqNum, 0, null, HearinglocationSeqNum), AsgnAttorneyEmplid, durationCode,
      ScheduletypeCode, SchedulestatusCode, keepAttorneyFlag,
      assignedDate, reminderDate, ScheduleText,
      createDate, createUser, asgnOtherFirmSeqNum,
      asgnPersonSeqNum, createUser, createDate);
     v_update_count := SQL%Rowcount;
    RETURN v_update_count;
  END insertSchedule;

  Function deleteSchedule(ScheduleSeqNum IN NUMBER)
      RETURN NUMBER
    IS
    v_update_count             NUMBER;
     v_legalCaseNum NUMBER;
     BEGIN
     UPDATE LEGAL_CASE
        SET most_recent_hearing_seq_num = NULL
      WHERE most_recent_hearing_seq_num  = ScheduleSeqNum;
     UPDATE APPEAL
        SET schedule_seq_num = NULL
      WHERE schedule_seq_num = ScheduleSeqNum;
     DELETE FROM ASSIGNMENT_RESULT WHERE schedule_seq_num = ScheduleSeqNum;
     DELETE FROM SCHEDULE WHERE schedule_seq_num = ScheduleSeqNum;
     v_update_count := SQL%Rowcount;
    RETURN v_update_count;
  END deleteSchedule;

  PROCEDURE getLegalCase(LegalCaseSeqNum IN NUMBER,
       ClaimId in Number,
       recordset IN OUT ref_cursor)

   AS BEGIN OPEN recordset FOR
      SELECT LEGAL_CASE_SEQ_NUM LegalCaseSeqNum,
      CASE_TYPE_CODE CaseTypeCode,
      REQUEST_TYPE_CODE RequestTypeCode,
      WCB_NUM WcbNum,
      get_case_num(LegalCaseSeqNum) AS CASE_NUM,
      DCBS_NUM DcbsNum,
      CASE_STATUS_CODE CaseStatusCode,
      CLAIM_ID ClaimID,
      REQUEST_DATE RequestDate,
      WORKER_ATTY_FIRM_PROV_LOC_ID WorkerAttyFirmProvLocID,
      WORKER_ATTY_PROV_LOC_ID WorkerAttyProvLocID,
      OAR_DUE_DATE OarDueDate,
      OAR_MAILED_DATE OarMailedDate,
      LEGAL_FILE_CREATE_DATE LegalFileCreateDate,
      CASE_WEIGHT_VALUE CaseWeightValue,
      ACKNOWLEDGE_DATE AcknowledgeDate,
      REQUEST_NEW_WCB_NUM_FLAG RequestNewWcbNumFlag,
      REQUESTED_BY_CODE RequestedBYCode,
      REQUEST_RECEIPT_DATE RequestReceiptDate,
      REQUEST_POSTMARK_DATE RequestPostmarkDate,
      REQUEST_ALL_DAY_HEARING_FLAG RequestAllDayHearingFlag,
      STRESS_CLAIM_FLAG StressClaimFlag,
      RECON_PACKET_RECEIVED_DATE ReconPacketReceivedDate,
      DISMISSAL_DATE DismissalDate,
      SETTLEMENT_DATE SettlementDate,
      SEND_DEF_FLAG SendDefFlag,
      RECON_LETTER_MAILED_DATE ReconLetterMailedDate,
      DEF_SENT_DATE DefSentDate,
      EMPLOYER_EVALUATION_NUM EmployerEvaluationNum,
      CONSOLIDATE_CASE_FLAG ConsolidateCaseFlag,
      OWCP_NUM OwcpNum,
      OALJ_NUM OaljNum,
      BRB_NUM BrbNum,
      CREATE_DATE CreateDate,
      CREATE_USER CreateUser,
      UPDATE_DATE UpdateDate,
      UPDATE_USER UpdateUser,
      MOST_RECENT_HEARING_SEQ_NUM as MostRecentHearingSeqNum,
      CLAIM_IMAGE_ALERTS_FLAG ClaimImageAlertsFlag,
      getClaimImageAlertsUpdateDate(LegalCaseSeqNum) AS ClaimImageAlertsUpdateDate
      FROM LEGAL_CASE C
      WHERE (legal_case_seq_num = LegalCaseSeqNum  and ClaimId = 0)
         OR (LegalCaseSeqNum = 0 and Claim_id = ClaimId);
END getLegalCase;

FUNCTION getClaimImageAlertsUpdateDate(aLegalCaseNum IN NUMBER)
RETURN DATE
IS
v_alert_date DATE;
BEGIN
     BEGIN
          SELECT MAX(UPDATE_DATE)
                 INTO v_alert_date
              FROM CLAIM_IMAGE_ALERTS_HISTORY H
          WHERE H.LEGAL_CASE_SEQ_NUM  = aLegalCaseNum;
     EXCEPTION
         WHEN NO_DATA_FOUND THEN
               NULL;
    END;
    RETURN v_alert_date;
END;

      PROCEDURE setFileCreateDate(seqNums IN VARCHAR2, inDate IN DATE,
          userid IN VARCHAR2)
          IS
          v_updatestmt VARCHAR2(5000);

          BEGIN

          v_updatestmt := 'Update legal_case ' ||
            ' set legal_file_create_date = ''' || inDate ||
            ''' , update_date =  sysdate '  ||
            ' , update_user = ''' || userid ||
            ''' where legal_case_seq_num in (' || seqNums || ')';
 --         dbms_output.put_line(v_updatestmt);
          EXECUTE IMMEDIATE v_updatestmt;

     END setFileCreateDate;

 PROCEDURE mailDateList(recordset OUT ref_cursor, inType IN VARCHAR2)
     AS BEGIN
     IF intype = 'OAR' THEN
     OPEN recordset FOR
     SELECT /*+ FIRST_ROWS */
            WCB_NUM wcbNum,
            OAR_DUE_DATE oarDueDate,
            OAR_MAILED_DATE oarMailedDate,
            CAS.LEGAL_CASE_SEQ_NUM caseId,
            CAS.CLAIM_ID claimId,
            'N' AS MAILED,
            'N' AS DONOTMAIL,
            DECODE(NVL(CON.PRIMARY_CASE_SEQ_NUM,CAS.LEGAL_CASE_SEQ_NUM),
                       CAS.LEGAL_CASE_SEQ_NUM,'Y','N') AS PRIMARYFLAG
     FROM LEGAL_CASE cas,
          CONSOLIDATED_CASE CON
    WHERE (OAR_DUE_DATE IS NOT NULL)
      AND (OAR_MAILED_DATE IS NULL)
      AND cas.CASE_STATUS_CODE <> 'CLOSE'
      and cas.mail_oar_flag = 'Y'
      AND CAS.LEGAL_CASE_SEQ_NUM = CON.SECONDARY_CASE_SEQ_NUM (+)
   ORDER BY OAR_DUE_DATE;
    ELSE
     OPEN recordset FOR
     SELECT /*+ FIRST_ROWS */
       WCB_NUM wcbNum,
       RECON_LETTER_MAILED_DATE AS OARDUEDATE,
       RECON_PACKET_RECEIVED_DATE AS OARMAILEDDATE,
       CAS.LEGAL_CASE_SEQ_NUM caseId,
       CAS.CLAIM_ID claimId,
       'N' AS MAILED,
       'N' AS DONOTMAIL,
       DECODE(NVL(CON.PRIMARY_CASE_SEQ_NUM,CAS.LEGAL_CASE_SEQ_NUM),
                  CAS.LEGAL_CASE_SEQ_NUM,'Y','N') AS PRIMARYFLAG
   FROM LEGAL_CASE CAS,
        CONSOLIDATED_CASE CON
  WHERE (RECON_LETTER_MAILED_DATE IS NOT NULL)
   AND (RECON_PACKET_RECEIVED_DATE IS NULL)
   AND cas.CASE_STATUS_CODE <> 'CLOSE'
     and cas.mail_oar_flag = 'Y'
   AND CAS.LEGAL_CASE_SEQ_NUM = CON.SECONDARY_CASE_SEQ_NUM (+)
ORDER BY RECON_LETTER_MAILED_DATE;
   END IF;

  END mailDateList;

  PROCEDURE setMailDate(seqNums IN VARCHAR2, inDate IN DATE,
           inType IN VARCHAR2, userid IN VARCHAR2)
     IS
          v_updatestmt VARCHAR2(5000);

          BEGIN
     IF inType = 'OAR' THEN
        v_updatestmt := 'Update legal_case ' ||
            ' set oar_mailed_date = ''' || inDate ||
            ''' , update_date =  sysdate ' ||
            ' , update_user = ''' || userid ||
            ''' where legal_case_seq_num in (' || seqNums || ')';
 --         dbms_output.put_line(v_updatestmt);
          EXECUTE IMMEDIATE v_updatestmt;
     ELSE
        v_updatestmt := 'Update legal_case ' ||
            ' set recon_packet_received_date = ''' || inDate ||
            ''' , update_date =  sysdate ' ||
            ' , update_user = ''' || userid ||
            ''' where legal_case_seq_num in (' || seqNums || ')';
 --         dbms_output.put_line(v_updatestmt);
            EXECUTE IMMEDIATE v_updatestmt;
     END IF;

     END setMailDate;

     Function setNoMailDate(InseqNum IN NUMBER, inDate IN DATE,
           inType IN VARCHAR2, userid IN VARCHAR2)
       RETURN NUMBER
    IS
    v_update_count             NUMBER;
    Begin
      IF inType = 'OAR' THEN
        UPDATE LEGAL_CASE
        SET oar_mailed_date =
         (SELECT oar_mailed_date
            FROM LEGAL_CASE
          WHERE legal_case_seq_num =
            (SELECT MAX(primary_case_seq_num)
               FROM CONSOLIDATED_CASE
              WHERE secondary_case_seq_num = InSeqNum)),
          update_date = SYSDATE,
          update_user = userid
         WHERE legal_case_seq_num = InSeqNum;
      ELSE
         UPDATE LEGAL_CASE
         SET recon_packet_received_date =
         (SELECT recon_packet_received_date
            FROM LEGAL_CASE
          WHERE legal_case_seq_num =
            (SELECT MAX(primary_case_seq_num)
               FROM CONSOLIDATED_CASE
              WHERE secondary_case_seq_num = InSeqNum)),
                update_date = SYSDATE,
                update_user = userid
                WHERE legal_case_seq_num = InseqNum;
      END IF;

         v_update_count := SQL%Rowcount;
    RETURN v_update_count;
     END setNoMailDate;

 Function  setMailOARFlag(InseqNum IN NUMBER, userid IN VARCHAR2)
       RETURN NUMBER
    IS
    v_update_count             NUMBER;
    Begin
     UPDATE LEGAL_CASE
        SET mail_oar_flag = 'N',
          update_date = SYSDATE,
          update_user = userid
         WHERE legal_case_seq_num = InSeqNum;

         v_update_count := SQL%Rowcount;
    RETURN v_update_count;
     END setMailOARFlag;
  PROCEDURE getAppeal(LegalCaseID IN NUMBER,
       Recordset OUT ref_cursor) AS
     BEGIN
      OPEN Recordset FOR
      SELECT appeal_seq_num AppealID,
       legal_case_seq_num LegalCaseID,
       REVIEW_LEVEL_CODE ReviewLevelCode,
       review_due_date ReviewDueDate,
       review_filed_date ReviewFiledDate,
       PETITIONER_RESPONDENT_CODE PetitionerRespondentCode,
       approved_flag ApprovedFlag,
       atty_emplid AttorneyEmployeeID,
       brief1_due_date Brief1DueDate,
       brief1_filed_date Brief1FiledDate,
       brief2_due_date Brief2DueDate,
       brief2_filed_date Brief2FiledDate,
       brief3_due_date Brief3DueDate,
       brief3_filed_date Brief3FiledDate,
       appeal_num AppealNumber,
       schedule_seq_num ScheduleID,
       create_date CreateDate,
       create_user CreateUser,
       update_date UpdateDate,
       update_user UpdateUser
     FROM APPEAL
       WHERE legal_case_seq_num = LegalCaseID
       ORDER BY appeal_seq_num;

     END getAppeal;

Function insertAppeal(AppealID IN NUMBER, LegalCaseID IN NUMBER,
              reviewLevel IN VARCHAR2, dueDate IN DATE, filedDate IN DATE,
              PetitionerRespondentCode IN VARCHAR2, approvedFlag IN VARCHAR2,
              attyEmplid IN VARCHAR2,
              brief1DueDate IN DATE, brief1FiledDate IN DATE,
              brief2DueDate IN DATE, brief2FiledDate IN DATE,
              brief3DueDate IN DATE, brief3FiledDate IN DATE,
              appealNum IN VARCHAR2, ScheduleID IN NUMBER,
              createDate IN DATE, createUser IN VARCHAR2)
               RETURN NUMBER
       AS BEGIN
           INSERT INTO APPEAL
      (appeal_seq_num, legal_case_seq_num, REVIEW_LEVEL_CODE,
       review_due_date, review_filed_date,
       PETITIONER_RESPONDENT_CODE, approved_flag,
       atty_emplid, brief1_due_date, brief1_filed_date,
       brief2_due_date, brief2_filed_date,
       brief3_due_date, brief3_filed_date,
       appeal_num, schedule_seq_num, create_date, create_user)
    VALUES
      (AppealID, LegalCaseID,
              reviewLevel, dueDate, filedDate,
              PetitionerRespondentCode, approvedFlag,
              attyEmplid,
              brief1DueDate, brief1FiledDate,
              brief2DueDate, brief2FiledDate,
              brief3DueDate, brief3FiledDate,
              appealNum, ScheduleID,
              createDate, createUser);
    Return SQL%Rowcount;
    EXCEPTION
         WHEN OTHERS THEN
           RAISE_APPLICATION_ERROR(-20004,
           'Error in insertAppeal: '||SQLERRM);

    END insertAppeal;

    Function updateAppeal(AppealID IN NUMBER, LegalCaseID IN NUMBER,
              reviewLevel IN VARCHAR2, dueDate IN DATE, filedDate IN DATE,
              PetitionerRespondentCode IN VARCHAR2, approvedFlag IN VARCHAR2,
              attyEmplid IN VARCHAR2,
              brief1DueDate IN DATE, brief1FiledDate IN DATE,
              brief2DueDate IN DATE, brief2FiledDate IN DATE,
              brief3DueDate IN DATE, brief3FiledDate IN DATE,
              appealNum IN VARCHAR2, ScheduleID IN NUMBER,
              updateDate IN DATE, updateUser IN VARCHAR2)
               RETURN NUMBER
       AS BEGIN
       UPDATE APPEAL
       SET REVIEW_LEVEL_CODE = reviewLevel,
       review_due_date = dueDate,
       review_filed_date = filedDate,
       PETITIONER_RESPONDENT_CODE = PetitionerRespondentCode,
       approved_flag = approvedFlag,
       atty_emplid = attyEmplid,
       brief1_due_date = brief1DueDate,
       brief1_filed_date = brief1FiledDate,
       brief2_due_date = brief2DueDate,
       brief2_filed_date = brief2FiledDate,
       brief3_due_date = brief3DueDate,
       brief3_filed_date = brief3FiledDate,
       appeal_num = appealNum,
       schedule_seq_num = ScheduleID,
       update_date = updatedate,
       update_user = updateUser
       WHERE appeal_seq_num = AppealID;
    Return SQL%Rowcount;
    END updateAppeal;

    PROCEDURE getAppealExtension(appealSeqNum IN NUMBER,
       Recordset OUT ref_cursor)

       AS BEGIN OPEN recordset FOR
       SELECT appeal_extension_seq_num,
              appeal_seq_num,
              request_date,
              days_requested
       FROM APPEAL_EXTENSION
       WHERE appeal_seq_num = appealSeqNum;

       END getAppealExtension;

   Function updateAppealExtension(
         appealExtSeqNum IN NUMBER,
         appealSeqNum    IN NUMBER,
         requestDate     IN DATE,
         daysRequested   IN NUMBER)
          RETURN NUMBER
         AS BEGIN
            UPDATE APPEAL_EXTENSION
               SET request_date = requestDate,
                   days_requested = daysRequested
            WHERE appeal_extension_Seq_num = appealExtSeqNum;
            Return SQL%Rowcount;
         END updateAppealExtension;

   Function insertAppealExtension(
         appealExtSeqNum IN NUMBER,
         appealSeqNum    IN NUMBER,
         requestDate     IN DATE,
         daysRequested   IN NUMBER)
          RETURN NUMBER
         AS BEGIN
         INSERT INTO APPEAL_EXTENSION
         (appeal_extension_seq_num,
          appeal_seq_num,
          request_date,
          days_requested)
         VALUES
          (appealExtSeqNum,
           appealSeqNum,
           requestDate,
           daysRequested);
           Return SQL%Rowcount;
         END insertAppealExtension;

   Function deleteappeal (inlegalcaseseqnum IN NUMBER, inappealseqnum IN NUMBER)
    RETURN NUMBER
AS
   v_atty           APPEAL.atty_emplid%TYPE;
   v_review_level   APPEAL.REVIEW_LEVEL_CODE%TYPE;
   v_sched_seq      APPEAL.schedule_seq_num%TYPE;
   v_prior_level    APPEAL.REVIEW_LEVEL_CODE%TYPE;
   v_prior_atty     APPEAL.atty_emplid%TYPE;
   v_prior_appeal   APPEAL.appeal_seq_num%TYPE;
   v_loc_emplid     LEGAL_FILE_LOCATION.location_person_emplid%TYPE;
   v_loc_code       LEGAL_FILE_LOCATION.LEGAL_FILE_LOCATION_CODE%TYPE;
   v_assgn_atty     APPEAL.atty_emplid%TYPE;
   v_cnt            Number;
   v_result         Number;
BEGIN
   BEGIN
      SELECT atty_emplid, NVL(REVIEW_LEVEL_CODE,' '), schedule_seq_num
        INTO v_atty, v_review_level, v_sched_seq
        FROM APPEAL
       WHERE appeal_seq_num = inappealseqnum;
   END;

   BEGIN
      DELETE FROM APPEAL_EXTENSION
            WHERE appeal_seq_num = inappealseqnum;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   END;

   BEGIN
      DELETE FROM APPEAL
            WHERE appeal_seq_num = inappealseqnum;
            v_result := SQL%Rowcount;
   EXCEPTION
      WHEN OTHERS THEN
         NULL;
   END;

   IF v_sched_seq IS NOT NULL THEN
      v_cnt := deleteschedule (v_sched_seq);
      updatehearingseqnum (inlegalcaseseqnum);
   END IF;

   BEGIN
      SELECT atty_emplid, REVIEW_LEVEL_CODE, appeal_seq_num
        INTO v_prior_atty, v_prior_level, v_prior_appeal
        FROM APPEAL
       WHERE appeal_seq_num = (SELECT MAX (appeal_seq_num)
                                 FROM APPEAL
                                WHERE legal_case_seq_num = inlegalcaseseqnum);
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   END;

   v_assgn_atty := get_asgn_atty_id (inlegalcaseseqnum);

   BEGIN
      SELECT location_person_emplid, LEGAL_FILE_LOCATION_CODE
        INTO v_loc_emplid, v_loc_code
        FROM LEGAL_FILE_LOCATION
       WHERE legal_case_seq_num = inlegalcaseseqnum
         AND legal_file_location_seq_num =
                               (SELECT MAX (legal_file_location_seq_num)
                                  FROM LEGAL_FILE_LOCATION
                                 WHERE legal_case_seq_num = inlegalcaseseqnum);
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   END;

   IF NVL(v_review_level,' ') != NVL(v_prior_level,' ')
   THEN
      popcasestatus (inlegalcaseseqnum);
   END IF;

   IF v_loc_emplid IS NOT NULL THEN
      IF v_loc_emplid != NVL(v_assgn_atty,0) THEN
         popfilelocation (inlegalcaseseqnum);
      END IF;
   ELSE
      IF v_loc_code = 'APEAL' THEN
         IF v_prior_appeal IS NULL THEN
            popfilelocation (inlegalcaseseqnum);
         ELSE
            IF v_prior_atty IS NOT NULL THEN
                popfilelocation (inlegalcaseseqnum);
            END IF;
        END IF;
      END IF;
   END IF;
   return v_result;
END deleteappeal;

PROCEDURE popcasestatus(inlegalcaseseqnum IN LEGAL_CASE.legal_case_seq_num%TYPE) AS
BEGIN
    DELETE FROM CASE_STATUS_HISTORY
    WHERE case_status_history_seq_num =
          (SELECT MAX(case_status_history_seq_num)
             FROM CASE_STATUS_HISTORY
            WHERE legal_case_seq_num = inlegalcaseseqnum);

    UPDATE LEGAL_CASE
      SET CASE_STATUS_CODE =
          (SELECT CASE_STATUS_CODE
             FROM CASE_STATUS_HISTORY
            WHERE case_status_history_seq_num =
                  (SELECT MAX(case_status_history_seq_num)
                     FROM CASE_STATUS_HISTORY
                    WHERE legal_case_seq_num = inlegalcaseseqnum))
      WHERE legal_case_seq_num = inlegalcaseseqnum;
END popcasestatus;

PROCEDURE popfilelocation(inlegalcaseseqnum IN LEGAL_CASE.legal_case_seq_num%TYPE) AS
BEGIN
    DELETE FROM LEGAL_FILE_LOCATION
     WHERE legal_file_location_seq_num =
           (SELECT MAX(legal_file_location_seq_num)
              FROM LEGAL_FILE_LOCATION
             WHERE legal_case_seq_num = inlegalcaseseqnum);
END popfilelocation;


     PROCEDURE getBoardLog(Recordset OUT ref_cursor)
    AS BEGIN OPEN recordset FOR
    SELECT cas.wcb_num WCBNum, CAS.LEGAL_CASE_SEQ_NUM caseId,
           PRC.CODE_DESCR PetitionerRespondent,
           A.brief1_due_date Brief1DueDate,
           A.brief2_due_date Brief2DueDate,
           cas.claim_id Claim_ID
      FROM LEGAL_CASE cas,
           APPEAL A,
           PETITIONER_RESPONDENT_CODE prc
     WHERE A.REVIEW_LEVEL_CODE = 'WCBA'
       AND A.approved_flag = 'Y'
       AND cas.CASE_STATUS_CODE <> 'CLOSE'
       AND A.atty_emplid IS NULL
       AND A.legal_case_seq_num = cas.legal_case_seq_num
       and A.PETITIONER_RESPONDENT_CODE = PRC.CODE;
    END getBoardLog;

    PROCEDURE updateAppealAtty(appealSeqNum IN NUMBER,
              attyEmplid IN VARCHAR2)
      AS BEGIN
      UPDATE APPEAL
         SET atty_emplid = attyEmplid
       WHERE appeal_seq_num = appealSeqNum;

    END updateAppealAtty;

    PROCEDURE getAcknowledgeList(recordset OUT ref_cursor, inType IN VARCHAR2)
    AS BEGIN OPEN recordset FOR
    SELECT L.LEGAL_CASE_SEQ_NUM caseId, L.WCB_NUM wcbNum,
           L.REQUEST_DATE RequestDate,
           get_case_issue_codes(l.legal_case_seq_num) AS Issues,
           L.CLAIM_ID claimId
  FROM LEGAL_CASE L
  WHERE L.CASE_STATUS_CODE <> 'CLOSE'
  AND (L.WCB_NUM IS NULL AND L.DCBS_NUM IS NULL)
  AND L.REQUEST_TYPE_CODE = inType
  ORDER BY REQUEST_DATE;

    END getAcknowledgeList;

   Function updateAcknowledged(inType IN VARCHAR2, inNum IN VARCHAR2,
       inDate IN DATE, CaseId IN NUMBER)
      RETURN NUMBER
    IS
    v_update_count             NUMBER;
   BEGIN
       IF inType = 'WCB' THEN
          UPDATE LEGAL_CASE
             SET wcb_num = inNum,
                 acknowledge_date = inDate
           WHERE legal_case_seq_num = CaseId;
       END IF;
       IF inType = 'DCBS' THEN
          UPDATE LEGAL_CASE
             SET dcbs_num = inNum,
                 acknowledge_date = inDate
           WHERE legal_case_seq_num = CaseId;
       END IF;
       v_update_count := SQL%Rowcount;
    RETURN v_update_count;
    END updateAcknowledged;

    PROCEDURE updateDEFfields(aLegalCaseNum IN NUMBER, inNum IN VARCHAR2,
       inDate IN DATE, inFlag IN VARCHAR2, inUser IN VARCHAR2)
       AS BEGIN
        UPDATE LEGAL_CASE
             SET employer_evaluation_num = inNum,
                 def_sent_date = inDate,
                 send_def_flag = inFlag,
                 update_user = inUser,
                 update_date = SYSDATE
           WHERE legal_case_seq_num = aLegalCaseNum;
    END updateDEFfields;

    PROCEDURE getFileLocation(LegalCaseID IN NUMBER,
       Recordset OUT ref_cursor)
       AS BEGIN OPEN recordset FOR
       SELECT legal_file_location_seq_num LegalFileLocationID,
              legal_case_seq_num LegalCaseID,
              location_person_emplid LocationPersonEmplid,
              LEGAL_FILE_LOCATION_CODE LegalFileLocationCode,
              move_date MoveDate,
              code_descr FileLocationDescr,
              LTRIM(RTRIM(adp.last_name)) || ', ' ||
              LTRIM(RTRIM(DECODE(NVL(LTRIM(RTRIM(adp.preferred_name)),''),'',first_name,
                 preferred_name))) AS FileLocationEmployee,
              LOC.CREATE_DATE CreateDate,
              LOC.CREATE_USER CreateUser
              FROM LEGAL_FILE_LOCATION loc,
                   LEGAL_FILE_LOCATION_CODE cod,
                   adp_employee adp
        WHERE legal_case_seq_num = LegalCaseID
          AND cod.code(+) = loc.LEGAL_FILE_LOCATION_CODE
          AND adp.emplid (+) = loc.location_person_emplid
        ORDER BY TRUNC(move_date) DESC, legal_file_location_seq_num DESC;

       END getFileLocation;

    Function updateFileLocation(
         LegalFileLocationID   IN NUMBER,
         LegalCaseID           IN NUMBER,
         LocationPersonEmplid  IN VARCHAR2,
         LegalFileLocationCode IN VARCHAR2,
         moveDate        IN DATE) return number
     AS BEGIN
        UPDATE LEGAL_FILE_LOCATION
           SET location_person_emplid = LocationPersonEmplid,
               LEGAL_FILE_LOCATION_CODE = LegalFileLocationCode,
               move_date = moveDate
         WHERE legal_file_location_seq_num = LegalFileLocationID;
     return SQL%Rowcount;
     END updateFileLocation;

     Function insertFileLocation(
         LegalFileLocationID   IN NUMBER,
         LegalCaseID           IN NUMBER,
         LocationPersonEmplid  IN VARCHAR2,
         LegalFileLocationCode IN VARCHAR2,
         moveDate        IN DATE,
         createDate      in date,
         createUser      in varchar2)
                  return number
         AS BEGIN
         INSERT INTO LEGAL_FILE_LOCATION
         (legal_file_location_seq_num,
          legal_case_seq_num, location_person_emplid,
          LEGAL_FILE_LOCATION_CODE,
          move_date,
          create_date,
          create_user)
          VALUES (LegalFileLocationID,
            LegalCaseID,
            LocationPersonEmplid,
            LegalFileLocationCode,
            moveDate,
            createDate,
            createUser);
         return SQL%Rowcount;
         END insertFileLocation;

   PROCEDURE getAttyProfile(Recordset OUT ref_cursor)
   AS BEGIN
   OPEN recordset FOR SELECT /*+ FIRST_ROWS */
    profile_emplid,
    adp.LAST_NAME || ', ' || DECODE(NVL(adp.preferred_name, ' '), ' ', adp.FIRST_NAME,adp.preferred_name) AS EMPLOYEE_NAME,
    eff_date,
    exp_date,
    ccu_claims_flag,
    ptd_claims_flag,
    ppd_claims_flag,
    confidential_claims_flag,
    health_issue_claims_flag,
    dcbs_claims_flag,
    federal_claims_flag,
    load_percent,
    primary_hear_loc_code,
    secondary_hear_loc_code,
    claim_team_code,
    ATTORNEY_TYPE_CODE,
    secretary_emplid,
    manager_emplid,
    tertiary_hear_loc_code,
    oregon_state_bar_number
  FROM LEGAL.ATTORNEY_PROFILE, adp_employee adp
  WHERE profile_emplid = adp.emplid;

  END getAttyProfile;

   Function insertAttyProfile(
       profileEmplid      IN   VARCHAR2,
       EffDate            IN   DATE,
       ExpDate            IN   DATE,
       CcuClaimsFlag      IN   VARCHAR2,
       PtdClaimsFlag      IN   VARCHAR2,
       PpdClaimsFlag      IN   VARCHAR2,
       ConfidentialClaimsFlag IN VARCHAR2,
       HealthIssueClaimsFlag  IN VARCHAR2,
       DcbsClaimsFlag         IN VARCHAR2,
       FederalClaimsFlag      IN VARCHAR2,
       LoadPercent            IN NUMBER,
       PrimaryHearLocCode     IN VARCHAR2,
       SecondaryHearLocCode   IN VARCHAR2,
       ClaimTeamCode          IN VARCHAR2,
       AttorneyTypeCode       IN VARCHAR2,
       SecretaryEmplid        IN VARCHAR2,
       ManagerEmplid          IN VARCHAR2,
       TertiaryHearLocCode    IN VARCHAR2,
       osbNumber              IN VARCHAR2)
        RETURN NUMBER
as
    v_update_count             NUMBER;
BEGIN
  INSERT INTO LEGAL.ATTORNEY_PROFILE
(
    profile_emplid,
    eff_date,
    exp_date,
    ccu_claims_flag,
    ptd_claims_flag,
    ppd_claims_flag,
    confidential_claims_flag,
    health_issue_claims_flag,
    dcbs_claims_flag,
    federal_claims_flag,
    load_percent,
    primary_hear_loc_code,
    secondary_hear_loc_code,
    claim_team_code,
    ATTORNEY_TYPE_CODE,
    secretary_emplid,
    manager_emplid,
    tertiary_hear_loc_code,
    create_date,
    CREATE_user,
    OREGON_STATE_BAR_NUMBER
)
VALUES
(  profileEmplid,
       EffDate,
       ExpDate,
       CcuClaimsFlag,
       PtdClaimsFlag,
       PpdClaimsFlag,
       ConfidentialClaimsFlag,
       HealthIssueClaimsFlag,
       DcbsClaimsFlag,
       FederalClaimsFlag,
       LoadPercent,
       PrimaryHearLocCode,
       SecondaryHearLocCode,
       ClaimTeamCode,
       AttorneyTypeCode,
       SecretaryEmplid,
       ManagerEmplid,
       tertiaryHearlocCode,
       SYSDATE,
       'LEGALNET',
       osbNumber);
    v_update_count := SQL%Rowcount;
    RETURN v_update_count;
   END insertAttyProfile;

  Function updateAttyProfile(
       profileEmplid      IN   VARCHAR2,
       EffDate            IN   DATE,
       ExpDate            IN   DATE,
       CcuClaimsFlag      IN   VARCHAR2,
       PtdClaimsFlag      IN   VARCHAR2,
       PpdClaimsFlag      IN   VARCHAR2,
       ConfidentialClaimsFlag IN VARCHAR2,
       HealthIssueClaimsFlag  IN VARCHAR2,
       DcbsClaimsFlag         IN VARCHAR2,
       FederalClaimsFlag      IN VARCHAR2,
       LoadPercent            IN NUMBER,
       PrimaryHearLocCode     IN VARCHAR2,
       SecondaryHearLocCode   IN VARCHAR2,
       ClaimTeamCode          IN VARCHAR2,
       AttorneyTypeCode       IN VARCHAR2,
       SecretaryEmplid        IN VARCHAR2,
       ManagerEmplid          IN VARCHAR2,
       tertiaryHearLocCode    IN VARCHAR2,
       osbNumber              IN VARCHAR2)
  RETURN NUMBER
  as
     v_update_count             NUMBER;
  BEGIN
    UPDATE LEGAL.ATTORNEY_PROFILE SET
    eff_date = effDate,
    ccu_claims_flag = ccuclaimsflag,
    ptd_claims_flag = ptdclaimsflag,
    ppd_claims_flag = ppdclaimsflag,
    confidential_claims_flag = confidentialclaimsflag,
    health_issue_claims_flag = healthissueclaimsflag,
    dcbs_claims_flag = dcbsclaimsflag,
    federal_claims_flag = federalclaimsflag,
    ATTORNEY_TYPE_CODE = attorneytypecode,
    load_percent = loadpercent,
    primary_hear_loc_code = primaryhearloccode,
    secondary_hear_loc_code = secondaryHearLocCode,
    claim_team_code = claimTeamCode,
    exp_date =  expDate,
    secretary_emplid = secretaryEmplid,
    manager_emplid =  managerEmplid,
    tertiary_hear_loc_code = tertiaryHearLocCode,
    update_date = SYSDATE,
    update_user = 'LEGALNET',
    OREGON_STATE_BAR_NUMBER = osbNumber
WHERE
    profile_emplid = profileEmplid;
    v_update_count := SQL%Rowcount;
    RETURN v_update_count;
  END updateAttyProfile;

 PROCEDURE getAttySchedule(Recordset OUT attySched_cursor)
 AS BEGIN OPEN recordset FOR
    SELECT /*+ FIRST_ROWS */ attorney_schedule_seq_num,
           schedule_emplid,
           eff_date,
           exp_date,
           ATTORNEY_SCHEDULE_TYPE_CODE,
           DURATION_CODE
      FROM ATTORNEY_SCHEDULE
     WHERE NVL(exp_date, SYSDATE) > SYSDATE - 1
     ORDER BY eff_date;
 END getAttySchedule;

    PROCEDURE updateAttySchedule(
         attySchedSeqNum IN NUMBER,
         scheduleEmplid  IN VARCHAR2,
         effDate         IN DATE,
         expDate         IN DATE,
         typeCode        IN VARCHAR2,
         durationCode    IN VARCHAR2)
     AS BEGIN
        UPDATE ATTORNEY_SCHEDULE
           SET schedule_emplid = scheduleEmplid,
               eff_date = effDate,
               exp_date = expdate,
               ATTORNEY_SCHEDULE_TYPE_CODE = typeCode,
               DURATION_CODE = durationCode
         WHERE attorney_schedule_seq_num = attySchedSeqNum;
     END updateAttySchedule;

     PROCEDURE insertAttySchedule(
       attySchedSeqNum IN NUMBER,
         scheduleEmplid  IN VARCHAR2,
         effDate         IN DATE,
         expDate         IN DATE,
         typeCode        IN VARCHAR2,
         durationCode   IN VARCHAR2)
      AS BEGIN
         INSERT INTO ATTORNEY_SCHEDULE
         (attorney_schedule_seq_num,
          schedule_emplid,
          eff_date,
          exp_date,
          ATTORNEY_SCHEDULE_TYPE_CODE,
          DURATION_CODE)
          VALUES( attySchedSeqNum,
           scheduleEmplid,
           effDate,
           expDate,
           typeCode,
           durationCode);
      END insertAttySchedule;

       PROCEDURE deleteAttySchedule(attySchedSeqNum IN NUMBER)
       AS BEGIN
       DELETE FROM ATTORNEY_SCHEDULE
       WHERE attorney_schedule_seq_num = attySchedSeqNum;

       END deleteAttySchedule;

Function InsertLegalCase(LegalCaseSeqNum in LEGAL_CASE.LEGAL_CASE_SEQ_NUM%TYPE,
                         CaseTypeCode IN LEGAL_CASE.REQUEST_TYPE_CODE%TYPE,
                         RequestTypeCode in LEGAL_CASE.REQUEST_TYPE_CODE%TYPE,
                         WcbNum in LEGAL_CASE.WCB_NUM%TYPE,
                         DcbsNum in LEGAL_CASE.DCBS_NUM%Type,
                         CaseStatusCode in LEGAL_CASE.CASE_STATUS_CODE%Type,
                         claimID        IN LEGAL_CASE.claim_id%TYPE,
                         RequestDate    IN LEGAL_CASE.request_date%TYPE,
                         WorkerAttyProvLocID in LEGAL_CASE.WORKER_ATTY_PROV_LOC_ID%Type,
                         WorkerAttyFirmProvLocID in LEGAL_CASE.WORKER_ATTY_FIRM_PROV_LOC_ID%Type,
                         OarDueDate      IN LEGAL_CASE.OAR_DUE_DATE%type,
                         OarMailedDate   IN LEGAL_CASE.OAR_MAILED_DATE%type,
                         LegalFileCreateDate IN LEGAL_CASE.LEGAL_FILE_CREATE_DATE%Type,
                         CaseWeightValue  IN LEGAL_CASE.CASE_WEIGHT_VALUE%Type,
                         AcknowledgeDate  IN LEGAL_CASE.ACKNOWLEDGE_DATE%type,
                         RequestNewWcbNumFlag IN LEGAL_CASE.REQUEST_ALL_DAY_HEARING_FLAG%Type,
                         RequestedByCode      IN LEGAL_CASE.REQUESTED_BY_CODE%TYPE,
                         RequestReceiptDate   in LEGAL_CASE.REQUEST_RECEIPT_DATE%Type,
                         RequestPostmarkDate  in LEGAL_CASE.REQUEST_POSTMARK_DATE%type,
                         RequestAllDayHearingFlag in LEGAL_CASE.REQUEST_ALL_DAY_HEARING_FLAG%type,
                         StressClaimFlag      in LEGAL_CASE.STRESS_CLAIM_FLAG%type,
                         ReconLetterMailedDate in LEGAL_CASE.RECON_LETTER_MAILED_DATE%type,
                         ReconPacketReceivedDate in LEGAL_CASE.RECON_PACKET_RECEIVED_DATE%type,
                         DismissalDate         in LEGAL_CASE.DISMISSAL_DATE%Type,
                         SettlementDate        in LEGAL_CASE.SETTLEMENT_DATE%Type,
                         SendDefFlag           in LEGAL_CASE.SEND_DEF_FLAG%type,
                         DefSentDate           in LEGAL_CASE.DEF_SENT_DATE%type,
                         EmployerEvaluationNum in LEGAL_CASE.EMPLOYER_EVALUATION_NUM%type,
                         ConsolidateCaseFlag   in LEGAL_CASE.CONSOLIDATE_CASE_FLAG%type,
                         OwcpNum               in LEGAL_CASE.OWCP_NUM%Type,
                         OaljNum               in LEGAL_CASE.OALJ_NUM%type,
                         BrbNum                in LEGAL_CASE.BRB_NUM%type,
                         CreateDate            in LEGAL_CASE.CREATE_DATE%type,
                         CreateUser            in LEGAL_CASE.CREATE_USER%type,
                         MostRecentHearingSeqNum in LEGAL_CASE.MOST_RECENT_HEARING_SEQ_NUM%type,
                         ClaimImageAlertsFlag   in LEGAL_CASE.CLAIM_IMAGE_ALERTS_FLAG%type)
         RETURN NUMBER
    IS
    v_update_count             NUMBER;
BEGIN
   Insert into
LEGAL_CASE
 (
            LEGAL_CASE_SEQ_NUM,
            CASE_TYPE_CODE,
            REQUEST_TYPE_CODE,
            WCB_NUM,
            DCBS_NUM,
            CASE_STATUS_CODE,
            CLAIM_ID,
            REQUEST_DATE,
            WORKER_ATTY_PROV_LOC_ID,
            WORKER_ATTY_FIRM_PROV_LOC_ID,
            OAR_DUE_DATE,
            OAR_MAILED_DATE,
            LEGAL_FILE_CREATE_DATE,
            CASE_WEIGHT_VALUE,
            ACKNOWLEDGE_DATE,
            REQUEST_NEW_WCB_NUM_FLAG,
            REQUESTED_BY_CODE,
            REQUEST_RECEIPT_DATE,
            REQUEST_POSTMARK_DATE,
            REQUEST_ALL_DAY_HEARING_FLAG,
            STRESS_CLAIM_FLAG,
            RECON_LETTER_MAILED_DATE,
            RECON_PACKET_RECEIVED_DATE,
            DISMISSAL_DATE,
            SETTLEMENT_DATE,
            SEND_DEF_FLAG,
            DEF_SENT_DATE,
            EMPLOYER_EVALUATION_NUM,
            CONSOLIDATE_CASE_FLAG,
            OWCP_NUM,
            OALJ_NUM,
            BRB_NUM,
            CREATE_DATE,
            CREATE_USER,
            MOST_RECENT_HEARING_SEQ_NUM,
            CLAIM_IMAGE_ALERTS_FLAG
 ) Values (
            LegalCaseSeqNum,
            CaseTypeCode,
            RequestTypeCode,
            WcbNum,
            DcbsNum,
            CaseStatusCode,
            ClaimID,
            RequestDate,
            WorkerAttyProvLocID,
            WorkerAttyFirmProvLocID,
            OarDueDate,
            OarMailedDate,
            LegalFileCreateDate,
            CaseWeightValue,
            AcknowledgeDate,
            RequestNewWcbNumFlag,
            RequestedBYCode,
            RequestReceiptDate,
            RequestPostmarkDate,
            RequestAllDayHearingFlag,
            StressClaimFlag,
            ReconLetterMailedDate,
            ReconPacketReceivedDate,
            DismissalDate,
            SettlementDate,
            SendDefFlag,
            DefSentDate,
            EmployerEvaluationNum,
            ConsolidateCaseFlag,
            OwcpNum,
            OaljNum,
            BrbNum,
            CreateDate,
            CreateUser,
            MostRecentHearingSeqNum,
            ClaimImageAlertsFlag);
    v_update_count := SQL%Rowcount;
    RETURN v_update_count;

END InsertLegalCase;

Function UpdateLegalCase(LegalCaseSeqNum in LEGAL_CASE.LEGAL_CASE_SEQ_NUM%TYPE,
                         CaseTypeCode IN LEGAL_CASE.REQUEST_TYPE_CODE%TYPE,
                         RequestTypeCode in LEGAL_CASE.REQUEST_TYPE_CODE%TYPE,
                         WcbNum in LEGAL_CASE.WCB_NUM%TYPE,
                         DcbsNum in LEGAL_CASE.DCBS_NUM%Type,
                         CaseStatusCode in LEGAL_CASE.CASE_STATUS_CODE%Type,
                         claimID        IN LEGAL_CASE.claim_id%TYPE,
                         RequestDate    IN LEGAL_CASE.request_date%TYPE,
                         WorkerAttyProvLocID in LEGAL_CASE.WORKER_ATTY_PROV_LOC_ID%Type,
                         WorkerAttyFirmProvLocID in LEGAL_CASE.WORKER_ATTY_FIRM_PROV_LOC_ID%Type,
                         OarDueDate      IN LEGAL_CASE.OAR_DUE_DATE%type,
                         OarMailedDate   IN LEGAL_CASE.OAR_MAILED_DATE%type,
                         LegalFileCreateDate IN LEGAL_CASE.LEGAL_FILE_CREATE_DATE%Type,
                         CaseWeightValue  IN LEGAL_CASE.CASE_WEIGHT_VALUE%Type,
                         AcknowledgeDate  IN LEGAL_CASE.ACKNOWLEDGE_DATE%type,
                         RequestNewWcbNumFlag IN LEGAL_CASE.REQUEST_ALL_DAY_HEARING_FLAG%Type,
                         RequestedByCode      IN LEGAL_CASE.REQUESTED_BY_CODE%TYPE,
                         RequestReceiptDate   in LEGAL_CASE.REQUEST_RECEIPT_DATE%Type,
                         RequestPostmarkDate  in LEGAL_CASE.REQUEST_POSTMARK_DATE%type,
                         RequestAllDayHearingFlag in LEGAL_CASE.REQUEST_ALL_DAY_HEARING_FLAG%type,
                         StressClaimFlag      in LEGAL_CASE.STRESS_CLAIM_FLAG%type,
                         ReconLetterMailedDate in LEGAL_CASE.RECON_LETTER_MAILED_DATE%type,
                         ReconPacketReceivedDate in LEGAL_CASE.RECON_PACKET_RECEIVED_DATE%type,
                         DismissalDate         in LEGAL_CASE.DISMISSAL_DATE%Type,
                         SettlementDate        in LEGAL_CASE.SETTLEMENT_DATE%Type,
                         SendDefFlag           in LEGAL_CASE.SEND_DEF_FLAG%type,
                         DefSentDate           in LEGAL_CASE.DEF_SENT_DATE%type,
                         EmployerEvaluationNum in LEGAL_CASE.EMPLOYER_EVALUATION_NUM%type,
                         ConsolidateCaseFlag   in LEGAL_CASE.CONSOLIDATE_CASE_FLAG%type,
                         OwcpNum               in LEGAL_CASE.OWCP_NUM%Type,
                         OaljNum               in LEGAL_CASE.OALJ_NUM%type,
                         BrbNum                in LEGAL_CASE.BRB_NUM%type,
                         UpdateDate            in LEGAL_CASE.CREATE_DATE%type,
                         UpdateUser            in LEGAL_CASE.CREATE_USER%type,
                         MostRecentHearingSeqNum in LEGAL_CASE.MOST_RECENT_HEARING_SEQ_NUM%type,
                         ClaimImageAlertsFlag   in LEGAL_CASE.CLAIM_IMAGE_ALERTS_FLAG%type)
                RETURN NUMBER
    IS
    v_update_count             NUMBER;
BEGIN
      Update LEGAL_CASE
      Set   CASE_TYPE_CODE =  CaseTypeCode,
            REQUEST_TYPE_CODE =  RequestTypeCode,
            WCB_NUM =  WcbNum,
            DCBS_NUM =  DcbsNum,
            CASE_STATUS_CODE =  CaseStatusCode,
            REQUEST_DATE =  RequestDate,
            WORKER_ATTY_PROV_LOC_ID =  WorkerAttyProvLocID,
            WORKER_ATTY_FIRM_PROV_LOC_ID =  WorkerAttyFirmProvLocID,
            OAR_DUE_DATE =  OarDueDate,
            OAR_MAILED_DATE =  OarMailedDate,
            LEGAL_FILE_CREATE_DATE =  LegalFileCreateDate,
            CASE_WEIGHT_VALUE =  CaseWeightValue,
            ACKNOWLEDGE_DATE =  AcknowledgeDate,
            REQUEST_NEW_WCB_NUM_FLAG =  RequestNewWcbNumFlag,
            REQUESTED_BY_CODE =  RequestedBYCode,
            REQUEST_RECEIPT_DATE =  RequestReceiptDate,
            REQUEST_POSTMARK_DATE =  RequestPostmarkDate,
            REQUEST_ALL_DAY_HEARING_FLAG =  RequestAllDayHearingFlag,
            STRESS_CLAIM_FLAG =  StressClaimFlag,
            RECON_LETTER_MAILED_DATE =  ReconLetterMailedDate,
            RECON_PACKET_RECEIVED_DATE =  ReconPacketReceivedDate,
            DISMISSAL_DATE =  DismissalDate,
            SETTLEMENT_DATE =  SettlementDate,
            SEND_DEF_FLAG =  SendDefFlag,
            DEF_SENT_DATE =  DefSentDate,
            EMPLOYER_EVALUATION_NUM =  EmployerEvaluationNum,
            CONSOLIDATE_CASE_FLAG =  ConsolidateCaseFlag,
            OWCP_NUM =  OwcpNum,
            OALJ_NUM =  OaljNum,
            BRB_NUM =  BrbNum,
            UPDATE_DATE =  UpdateDate,
            UPDATE_USER =  UpdateUser,
            MOST_RECENT_HEARING_SEQ_NUM =  MostRecentHearingSeqNum,
            CLAIM_IMAGE_ALERTS_FLAG =  ClaimImageAlertsFlag
    Where LEGAL_CASE.LEGAL_CASE_SEQ_NUM =  LegalCaseSeqNum AND
          LEGAL_CASE.CLAIM_ID =  ClaimID;
   v_update_count := SQL%Rowcount;
    RETURN v_update_count;

END UpdateLegalCase;
PROCEDURE CREATE_REQUEST_ISSUE(in_legal_case_seq IN LEGAL_CASE.legal_Case_seq_num%TYPE,
                               in_issue_code     IN REQUEST_ISSUE.REQUEST_ISSUE_CODE%TYPE,
                               in_issue_text     IN REQUEST_ISSUE.REQUEST_ISSUE_TEXT%TYPE) AS
BEGIN
INSERT INTO LEGAL.REQUEST_ISSUE
(
    request_issue_seq_num,
    legal_case_seq_num,
    REQUEST_ISSUE_CODE,
    request_issue_text
)
VALUES
(
    request_issue_Seq.NEXTVAL,
    in_legal_case_Seq,
    in_issue_code,
    in_issue_text
);

END   CREATE_REQUEST_ISSUE;

PROCEDURE UPDATE_LEGALCASE_REQUEST(aLegalCaseSeq IN NUMBER,
                                   aIssueType IN LEGAL_CASE.REQUEST_TYPE_CODE%TYPE,
                                   aWCBRequested IN LEGAL_CASE.request_new_wcb_num_flag%TYPE,
                                   aStress IN LEGAL_CASE.stress_claim_flag%TYPE,
                                   aRequestedBy IN LEGAL_CASE.REQUESTED_BY_CODE%TYPE,
                                   aRequestDate IN VARCHAR2,
                                   aReceivedDate IN VARCHAR2,
                                   aPostmarkDate IN VARCHAR2,
                                   aAttyFirmProvID IN NUMBER,
                                   aReconLetter IN VARCHAR2,
                                   aAttyProvID IN NUMBER,
                                   aLCEExcused IN VARCHAR2,
                                   aOWCP IN VARCHAR2,
                                   aUserID IN VARCHAR2) AS
v_postmark DATE;
v_OARDue DATE;
v_recon_cnt NUMBER;
v_case_weight NUMBER;
--v_lce VARCHAR2(1);

BEGIN

--   v_lce := aLCEExcused;
-- Determine if this is a Reconsideration
   SELECT COUNT(*)
     INTO v_recon_cnt
     FROM REQUEST_ISSUE
    WHERE legal_case_seq_num = aLegalCaseSeq
      AND LTRIM(RTRIM(REQUEST_ISSUE_CODE)) IN
          (SELECT code
            FROM REQUEST_ISSUE_CODE
           WHERE recon_issue_flag = 'Y');

 -- Calculate OAR Due Date
 IF aIssueType = 'WCB' THEN
    IF v_recon_cnt = 0 THEN
       v_postmark :=  TO_DATE(aPostmarkDate, 'MM/DD/YYYY');
       IF NVL(v_postmark,'01-jan-1900') > '01-jan-1900' THEN
          v_OARDue := v_postmark + 10;
       END IF;
    END IF;
-- ELSE
    -- Set LCE Excused
--    v_lce := 'Y';
 END IF;
IF aStress = 'Y' THEN
     v_case_weight := 3;
ELSE
     v_case_weight := 1;
END IF;
BEGIN
UPDATE LEGAL_CASE
  SET  REQUEST_TYPE_CODE = aIssueType,
       request_new_wcb_num_flag = aWCBRequested,
       stress_claim_flag = aStress,
       case_weight_value = v_case_weight,
       REQUESTED_BY_CODE = aRequestedBy,
       request_date = TO_DATE(aRequestDate,'MM/DD/YYYY'),
       request_receipt_date = TO_DATE(aReceivedDate, 'MM/DD/YYYY'),
       request_postmark_date = TO_DATE(aPostmarkDate, 'MM/DD/YYYY'),
       worker_atty_firm_prov_loc_id = aAttyFirmProvID,
       oar_due_date = v_OARDue,
       worker_atty_prov_loc_id = aAttyProvID,
       owcp_num = aOWCP
 WHERE legal_case_seq_num = aLegalCaseSeq;
END;
END UPDATE_LEGALCASE_REQUEST;

PROCEDURE update_lce_Excused(aLegalCaseSeq IN NUMBER,
                             aLCEExcused IN VARCHAR2,
                             aUserID IN VARCHAR2) AS
v_lce_seq NUMBER := 0;
v_lce VARCHAR2(1);
v_lce_excused_flag VARCHAR2(1);
v_result number;
BEGIN
  v_lce := aLceExcused;
  -- Find LCE Row if Exists
  BEGIN
   SELECT legal_case_evaluation_seq_num, LCE_EXCUSED_FLAG
        INTO v_lce_seq, v_lce_excused_flag
        FROM LEGAL_CASE_EVALUATION
       WHERE legal_case_seq_num = aLegalCaseSeq;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
        NULL;
  END;

  -- IF LCE Exists, Update w/ Change
  IF v_lce_seq > 0 THEN
     IF v_lce <> v_lce_excused_flag THEN
        UPDATE LEGAL_CASE_EVALUATION
           SET lce_excused_flag = v_lce,
               update_date = SYSDATE,
               update_user = aUSERID;
     END IF;
  ELSE
     -- If no LCE row, Insert if LCE is Excused
     IF v_lce = 'Y' THEN
        SELECT legal_case_evaluation_seq.NEXTVAL
          INTO v_lce_seq
          FROM dual;
      v_result :=  insertLCE(v_lce_seq,
                  aLegalCaseSeq,
                  v_lce,
                  NULL, NULL,
                  NULL, NULL,
                  SYSDATE, aUserID, NULL);
     END IF;
  END IF;
END update_lce_Excused;

PROCEDURE getConsolidatedCases(recordset OUT ref_cursor,
                        claimIdsCommaDelimited IN VARCHAR2) AS
  BEGIN
      BEGIN
      OPEN recordset FOR
      SELECT /*+ FIRST_ROWS */ cas.legal_case_seq_num,
             get_case_num(cas.legal_case_seq_num) wcb_num,
             cas.claim_id,
             NVL(con.consolidated_case_Seq_num,0) AS consolidated_case_Seq_num,
             NVL(con.primary_case_seq_num,0) AS primary_case_seq_num,
             NVL(con.secondary_case_seq_num, 0) AS secondary_case_seq_num,
             cod.code_descr AS case_status,
             cas.CASE_STATUS_CODE,
             NVL(con.create_date, sysdate) createDate,
             con.create_user createUser
       FROM LEGAL_CASE cas,
            CONSOLIDATED_CASE con,
            CASE_STATUS_CODE cod
        WHERE cas.claim_id IN (
               -- https://blogs.oracle.com/sql/post/split-comma-separated-values-into-rows-in-oracle-database
               with claimsString as (
                  select claimIdsCommaDelimited str from dual
               )
               select regexp_substr (claimIdsCommaDelimited, '[^,]+', 1, level) value
               from   claimsString
               connect by level <=
               length ( claimIdsCommaDelimited ) - length ( replace ( claimIdsCommaDelimited, ',' ) ) + 1
            )
          AND ( (cas.CASE_STATUS_CODE <> 'CLOSE' AND  DECODE(con.secondary_case_seq_num, cas.legal_case_seq_num,1,0)  = 0)
                          OR
                          DECODE(con.secondary_case_seq_num, cas.legal_case_seq_num,1,0) = 1)
          AND cod.code = cas.CASE_STATUS_CODE
          AND con.secondary_case_Seq_num(+) = cas.legal_case_seq_num
     ORDER BY decode(primary_case_seq_num, 0, 9999999, primary_case_seq_num),
      decode(secondary_case_seq_num, 0, cas.legal_case_seq_num, secondary_case_seq_num);
       EXCEPTION
            WHEN NO_DATA_FOUND THEN
            NULL;
      END;

  END getConsolidatedCases;

  PROCEDURE UpdateConsolidatedCase(aConsolidatedSeq IN CONSOLIDATED_CASE.consolidated_case_seq_num%TYPE,
                                   aPrimarySeq IN CONSOLIDATED_CASE.primary_case_seq_num%TYPE,
                                   aSecondarySeq IN CONSOLIDATED_CASE.secondary_case_seq_num%TYPE,
                                   aConsolFlag IN VARCHAR2,
                                   UpdateDate in DATE,
                                   UpdateUser in Varchar2) AS
  v_old_primary NUMBER;
  v_hearing_seq NUMBER;
  BEGIN

  -- Find the prior Primary Seq
  BEGIN
       SELECT MAX(primary_case_seq_num)
         INTO v_old_primary
         FROM CONSOLIDATED_CASE
        WHERE secondary_case_seq_num = aSecondarySeq;
  EXCEPTION
      WHEN NO_DATA_FOUND THEN
           v_old_primary := 0;
  END;

  IF  v_old_primary > 0
  AND aPrimarySeq   > 0 THEN

       -- Change the Primary Seq on all rows in set
       IF v_old_primary <> aPrimarySeq THEN
          UPDATE CONSOLIDATED_CASE
             SET primary_case_Seq_num = aPrimarySeq
           WHERE primary_case_seq_num = v_old_primary;

          -- Change the hearing to the new primary case
          UPDATE SCHEDULE
            SET  legal_case_seq_num = aPrimarySeq
          WHERE  schedule_seq_num =
                (SELECT most_recent_hearing_seq_num
                   FROM LEGAL_CASE
                 WHERE  legal_case_seq_num = v_old_primary);
       END IF;
  END IF;

  IF aConsolFlag = 'Y' THEN

     -- Update if this is an existing row
     IF aConsolidatedSeq > 0 THEN

      UPDATE CONSOLIDATED_CASE
          SET primary_case_seq_num = aPrimarySeq
             ,secondary_case_seq_num = aSecondarySeq
             , update_date = updatedate
             , update_user = updateuser
        WHERE consolidated_Case_Seq_num = aConsolidatedSeq;

    ELSE
       INSERT INTO CONSOLIDATED_CASE
          (CONSOLIDATED_CASE_SEQ_NUM,
           PRIMARY_CASE_SEQ_NUM,
           SECONDARY_CASE_SEQ_NUM,
           create_date,
           create_user)
       VALUES
          (consolidated_case_seq.NEXTVAL,
           aPrimarySeq,
           aSecondarySeq,
           updatedate,
           updateuser);
    END IF;

     -- Make sure consolidate flag is checked
  UPDATE LEGAL_CASE
     SET consolidate_case_flag = 'Y',
         most_recent_hearing_seq_num =
         (SELECT most_recent_hearing_seq_num
            FROM LEGAL_CASE
           WHERE  legal_case_seq_num = aPrimarySeq),
        CASE_STATUS_CODE =
          (SELECT CASE_STATUS_CODE
             FROM LEGAL_CASE
            WHERE  legal_case_seq_num = aPrimarySeq)
     WHERE legal_case_seq_num = aPrimarySeq
        OR legal_case_seq_num = aSecondarySeq;


 END IF;

  -- Delete if consolidate flag un-checked
  IF aConsolFlag = 'N' THEN
    DELETE FROM CONSOLIDATED_CASE
     WHERE CONSOLIDATED_CASE_SEQ_NUM = aConsolidatedSeq;

    -- Reset most recent hearing seq num
    UPDATE LEGAL_CASE
       SET CONSOLIDATE_CASE_FLAG = 'N',
           MOST_RECENT_HEARING_SEQ_NUM =
           (SELECT MAX(schedule_seq_num)
              FROM SCHEDULE WHERE
             SCHEDULE_TYPE_CODE = 'HEAR'
             AND legal_case_seq_num = aSecondarySeq
             AND schedule_date =
                 (SELECT MAX(schedule_date) FROM
                   SCHEDULE
                   WHERE SCHEDULE_TYPE_CODE = 'HEAR'
             AND legal_case_seq_num = aSecondarySeq))
       WHERE LEGAL_CASE_SEQ_NUM = aSecondarySeq;
  END IF;

  END UpdateConsolidatedCase;

  PROCEDURE getAttorneyNames(recordset OUT attorney_cursor) AS
  BEGIN
  BEGIN
  OPEN recordset FOR
      SELECT trim(last_name) || ', ' || trim(first_name) AS atty_name,
             per.legal_person_Seq_num
        FROM LEGAL_PERSON per, LEGAL_PERSON_TYPE lpt
       WHERE per.legal_person_seq_num = lpt.legal_person_seq_num
         AND lpt.LEGAL_PERSON_TYPE_CODE = 'ATTY'
       UNION
      SELECT '-- None --', 0
        FROM dual
       ORDER BY 1;
         EXCEPTION
      WHEN NO_DATA_FOUND THEN
      NULL;
  END;
END getAttorneyNames;

PROCEDURE insertLetterRequest(
         letterTypeCode  IN VARCHAR2,
         aLegalCaseNum   IN NUMBER,
         holdDate        IN DATE,
         aClaimId        IN NUMBER,
         createUser      IN VARCHAR2,
         requestNum      IN OUT NUMBER)
  AS
   v_send     VARCHAR2(3);
   BEGIN

   v_send := 'YES';

  SELECT letter_request_seq.NEXTVAL
    INTO requestNum
    FROM dual;

  INSERT INTO LETTER_REQUEST
   (letter_request_Seq_num,
    LETTER_TYPE_CODE,
    legal_case_seq_num,
    hold_date,
    create_user,
    create_date)
  VALUES(requestNum,
    letterTypeCode,
    aLegalCaseNum,
    holdDate,
    createUser,
    sysdate);

  END insertLetterRequest;

PROCEDURE getHearingInfo(in_HearingSeq IN SCHEDULE.schedule_seq_num%TYPE,
                         recordset    OUT HearingCursor) AS
BEGIN
OPEN recordset FOR
     SELECT sch.schedule_seq_num, sch.schedule_date, sch.schedule_time,
     sch.DURATION_CODE, DECODE(sch.keep_attorney_flag, 'Y',1,0) AS keep_attorney_flag,
     sch.assigned_date, sch.reminder_date, sch.schedule_text,
     aty.user_id AS atty_mailid,
     sec.user_id AS sec_mailid,
     LTRIM(RTRIM(sec.first_name)) || ' ' || LTRIM(RTRIM(sec.last_name)) AS sec_name,
     LTRIM(RTRIM(per.last_name)) AS alj_name, pro.secretary_emplid AS sec_emplid,
     loc.HEARING_LOCATION_CODE, lcd.code_descr AS hearing_loc_city,
     loc.location_name, ssc.code_descr AS hearing_status,
     DECODE(cas.request_all_day_hearing_flag,'Y',1,0) AS All_day_hearing_flag
FROM SCHEDULE sch,
     ATTORNEY_PROFILE pro,
     adp_employee aty,
     adp_employee sec,
     LEGAL_PERSON per,
     HEARING_LOCATION loc,
     HEARING_LOCATION_CODE lcd,
     SCHEDULE_STATUS_CODE ssc,
     LEGAL_CASE cas
WHERE sch.schedule_seq_num = in_HearingSeq
  AND aty.emplid(+) = sch.asgn_attorney_emplid
  AND pro.profile_emplid(+) = sch.asgn_attorney_emplid
  AND sec.emplid(+) = pro.secretary_emplid
  AND per.legal_person_seq_num(+) = sch.alj_person_seq_num
  AND loc.hearing_location_seq_num(+) = sch.hearing_location_seq_num
  AND lcd.code(+) = loc.HEARING_LOCATION_CODE
  AND ssc.code(+) = sch.SCHEDULE_STATUS_CODE
  AND cas.legal_case_Seq_num = sch.legal_case_seq_num;

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
         NULL;
END getHearingInfo;

PROCEDURE getEmployerAddr(in_Policy IN POLICY.policy_num%TYPE,
                         recordset    OUT EmployerCursor) AS
BEGIN
OPEN recordset FOR
SELECT P.policy_name, l.Loc_addr1, l.loc_addr2, l.loc_city, l.loc_state,
       l.loc_zip,l.loc_zip4, l.mail_addr_flag,
       l.loc_phone || ' ' || l.loc_phone_ext AS loc_phone, l.loc_seq_num
 FROM POLICY P,
      LOCATION l
WHERE P.policy_num = in_policy
  AND l.policy_num = P.policy_num
  AND l.primary_location_flag = 'Y'
  AND TRUNC(l.loc_eff_date) <= TRUNC(SYSDATE)
  AND NVL(TRUNC(l.loc_exp_date), SYSDATE + 1) > TRUNC(SYSDATE);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
         NULL;
END getEmployerAddr;

PROCEDURE getOtherFirmList(aLegalCaseNum IN NUMBER,
                      recordset OUT OtherFirmListCursor) AS
BEGIN
OPEN recordset FOR
SELECT 'EMPLOYER' AS type_cd,
       emp.legal_case_employer_seq_num,
       emp.employer_other_firm_seq_num,
       --emp.firm_provider_num,
       DECODE(emp.employer_discovery_flag,'Y',1,0) AS  employer_discovery_flag,
       DECODE(emp.attorney_discovery_flag,'Y',1,0) AS  attorney_discovery_flag,
       oth.firm_name,
       oth.OTHER_FIRM_TYPE_CODE,
       oth.addr1,
       oth.addr2,
       oth.city,
       oth.state,
       oth.zip,
       oth.zip4,
       oth.PHONE,
       oth.fax
  FROM LEGAL_CASE_EMPLOYER emp,
       OTHER_FIRM oth
 WHERE oth.other_firm_seq_num = emp.employer_other_firm_seq_num
   AND emp.legal_case_seq_num = aLegalCaseNum;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
         NULL;
END getOtherFirmList;

PROCEDURE GetEmployeebyMailid(in_MailId IN VARCHAR,
                      recordset OUT EmployeeCursor) AS
BEGIN
OPEN recordset FOR
SELECT
    EMPLID,
    LAST_NAME,
    FIRST_NAME,
    MIDDLE_NAME,
    EMAIL_ADDRESS,
    EMPL_STATUS,
    PHONE,
    PHONE_EXTENSION,
    LOCATION,
    PREFERRED_NAME,
    DEPTID,
    DESCR,
    REPORTS_TO,
    USER_ID
FROM LEGAL.ADP_EMPLOYEE
WHERE USER_ID = in_MailId;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
         NULL;
END GetEmployeebyMailid;

FUNCTION GetEmployeeName(in_mailid IN VARCHAR)
  RETURN VARCHAR2
AS
v_full_name VARCHAR2(50);
v_last_name adp_employee.last_name%TYPE;
v_first_name adp_employee.first_name%TYPE;
v_middle_name adp_employee.middle_name%TYPE;
v_preferred_name adp_employee.preferred_name%TYPE;
BEGIN
  BEGIN
    SELECT
      LTRIM(RTRIM(LAST_NAME)),
      LTRIM(RTRIM(FIRST_NAME)),
      LTRIM(RTRIM(MIDDLE_NAME)),
      LTRIM(RTRIM(PREFERRED_NAME))
     INTO v_last_name,
          v_first_name,
          v_middle_name,
          v_preferred_name
     FROM LEGAL.ADP_EMPLOYEE
    WHERE USER_ID = LTRIM(RTRIM(in_MailId));
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
         NULL;
END;

IF v_preferred_name IS NULL THEN
   v_full_name := v_first_name || ' ' || v_last_name;
ELSE
   v_full_name := v_preferred_name || ' ' || v_last_name;
END IF;

RETURN v_full_name;
END GetEmployeeName;


PROCEDURE IsLetterRequested(aLegalCaseNum IN NUMBER,
                           aLetterType   IN VARCHAR2,
                           out_requested OUT VARCHAR2)
AS
v_cnt NUMBER;
BEGIN

out_requested := 'N';

SELECT COUNT(*)
  INTO v_cnt
  FROM LETTER_REQUEST
 WHERE LETTER_TYPE_CODE = aLetterType
   AND legal_case_seq_num = aLegalCaseNum
   AND printed_date IS NULL;

IF v_cnt > 0 THEN
   out_requested := 'Y';
END IF;

END  IsLetterRequested;

PROCEDURE GetCaseCost(aLegalCaseNum IN  NUMBER,
                      recordset OUT CaseCostCursor)
 AS BEGIN
 OPEN recordset FOR
 SELECT case_cost_seq_num
      , legal_case_seq_num
      , service_from_date
      , service_to_date
      , SERVICE_CODE
      , amount_paid
      , amount_billed
      , amount_per_hour
      , hours_billed
      , DECODE(prepayment_flag, 'Y', 'True', 'False') AS prepayment_flag
      , authorized_date
      , payee_firm_prov_loc_id
      , payee_person_prov_loc_id
      , authorizer_emplid
      , CANCELLED_BY_CODE
      , CREATE_DATE
      , CREATE_USER
      , UPDATE_DATE
      , UPDATE_USER
   FROM CASE_COST
  WHERE legal_case_seq_num = aLegalCaseNum
  ORDER BY service_from_date;

 END getCaseCost;

 PROCEDURE case_cost_update
(
    case_cost_seq_num_in IN CASE_COST.case_cost_seq_num%TYPE,
    legal_case_seq_num_in IN CASE_COST.legal_case_seq_num%TYPE,
    service_from_date_in IN CASE_COST.service_from_date%TYPE,
    service_to_date_in IN CASE_COST.service_to_date%TYPE,
    service_code_in IN CASE_COST.SERVICE_CODE%TYPE,
    amount_paid_in IN CASE_COST.amount_paid%TYPE,
    amount_billed_in IN CASE_COST.amount_billed%TYPE,
    amount_per_hour_in IN CASE_COST.amount_per_hour%TYPE,
    hours_billed_in IN    CASE_COST.hours_billed%TYPE,
    prepayment_flag_in IN CASE_COST.prepayment_flag%TYPE,
    authorized_date_in IN CASE_COST.authorized_date%TYPE,
    payee_firm_prov_loc_id_in IN CASE_COST.PAYEE_firm_PROV_LOC_ID%TYPE,
    payee_person_prov_loc_id_in IN CASE_COST.payee_person_prov_loc_id%TYPE,
    authorizer_emplid_in IN CASE_COST.authorizer_emplid%TYPE,
    cancelled_by_code_in IN CASE_COST.CANCELLED_BY_CODE%TYPE,
    update_date_in IN CASE_COST.update_date%TYPE,
    update_user_in IN CASE_COST.update_user%TYPE
)
IS
BEGIN
    UPDATE LEGAL.CASE_COST SET
        legal_case_seq_num = legal_case_seq_num_in,
        service_from_date = service_from_date_in,
        service_to_date = service_to_date_in,
        SERVICE_CODE = service_code_in,
        amount_paid = amount_paid_in,
        amount_billed = amount_billed_in,
        amount_per_hour = amount_per_hour_in,
        hours_billed = hours_billed_in,
        prepayment_flag = prepayment_flag_in,
        authorized_date = authorized_date_in,
        payee_firm_prov_loc_id = payee_firm_prov_loc_id_in,
        PAYEE_PERSON_PROV_LOC_ID = payee_person_prov_loc_id_in,
        authorizer_emplid = authorizer_emplid_in,
        CANCELLED_BY_CODE = cancelled_by_code_in,
        update_date = update_date_in,
        update_user = update_user_in
    WHERE
        case_cost_seq_num = case_cost_seq_num_in;
END case_cost_update;

/********************************************
** INSERT PROCEDURE FOR TABLE "LEGAL.CASE_COST"
** DATE: 28-JUL-03 13:24:16
*********************************************/
PROCEDURE case_cost_insert
(
    case_cost_seq_num_in IN CASE_COST.case_cost_seq_num%TYPE,
    legal_case_seq_num_in IN CASE_COST.legal_case_seq_num%TYPE,
    service_from_date_in IN CASE_COST.service_from_date%TYPE,
    service_to_date_in IN CASE_COST.service_to_date%TYPE,
    service_code_in IN CASE_COST.SERVICE_CODE%TYPE,
    amount_paid_in IN CASE_COST.amount_paid%TYPE,
    amount_billed_in IN CASE_COST.amount_billed%TYPE,
    amount_per_hour_in IN CASE_COST.amount_per_hour%TYPE,
    hours_billed_in IN    CASE_COST.hours_billed%TYPE,
    prepayment_flag_in IN CASE_COST.prepayment_flag%TYPE,
    authorized_date_in IN CASE_COST.authorized_date%TYPE,
    payee_firm_prov_loc_id_in IN CASE_COST.PAYEE_firm_PROV_LOC_ID%TYPE,
    payee_person_prov_loc_id_in IN CASE_COST.payee_person_prov_loc_id%TYPE,
    authorizer_emplid_in IN CASE_COST.authorizer_emplid%TYPE,
    cancelled_by_code_in IN CASE_COST.CANCELLED_BY_CODE%TYPE,
    create_date_in IN CASE_COST.create_date%TYPE,
    create_user_in IN CASE_COST.create_user%TYPE)
IS
BEGIN
    INSERT INTO LEGAL.CASE_COST
    (
        case_cost_seq_num,
        legal_case_seq_num,
        service_from_date,
        service_to_date,
        SERVICE_CODE,
        amount_paid,
        amount_billed,
        amount_per_hour,
        hours_billed,
        prepayment_flag,
        authorized_date,
        payee_firm_prov_loc_id,
        PAYEE_PERSON_PROV_LOC_ID,
        authorizer_emplid,
        CANCELLED_BY_CODE,
        create_date,
        create_user
    )
    VALUES
    (
        case_cost_seq_num_in,
        legal_case_seq_num_in,
        service_from_date_in,
        service_to_date_in,
        service_code_in,
        amount_paid_in,
        amount_billed_in,
        amount_per_hour_in,
        hours_billed_in,
        prepayment_flag_in,
        authorized_date_in,
        payee_firm_prov_loc_id_in,
        payee_person_prov_loc_id_in,
        authorizer_emplid_in,
        cancelled_by_code_in,
        create_date_in,
        create_user_in
    );
END case_cost_insert;

/********************************************
** DELETE PROCEDURE FOR TABLE "LEGAL.CASE_COST"
** DATE: 02-DEC-03 16:12:45
*********************************************/
PROCEDURE case_cost_delete
(
    case_cost_seq_num_in IN CASE_COST.case_cost_seq_num%TYPE
)
IS
BEGIN
    DELETE FROM CASE_COST
    WHERE
        case_cost_seq_num = case_cost_seq_num_in;
END case_cost_delete;

Function DeleteLegalCase(LegalCaseSeqNum IN LEGAL_CASE.legal_case_Seq_num%TYPE)
  RETURN NUMBER
IS
   v_update_count             NUMBER;
BEGIN

-- Delete foreign keys first
BEGIN
DELETE FROM LEGAL_FILE_LOCATION
 WHERE legal_case_seq_num = LegalCaseSeqNum;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
DELETE FROM LEGAL_CASE_EVALUATION
 WHERE legal_case_Seq_num = LegalCaseSeqNum;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
DELETE FROM CONSOLIDATED_CASE
 WHERE primary_case_Seq_num = LegalCaseSeqNum
    OR secondary_case_seq_num = LegalCaseSeqNum;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
DELETE FROM REQUEST_ISSUE
 WHERE legal_case_Seq_num = LegalCaseSeqNum;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
DELETE FROM LETTER_REQUEST
 WHERE legal_case_Seq_num = LegalCaseSeqNum;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
DELETE FROM LEGAL_CASE_EVALUATION
 WHERE legal_case_Seq_num = LegalCaseSeqNum;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
DELETE FROM CASE_STATUS_HISTORY
 WHERE legal_case_Seq_num = LegalCaseSeqNum;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
DELETE FROM LEGAL_CASE_EMPLOYER_CARRIER
 WHERE legal_case_Seq_num = LegalCaseSeqNum;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
DELETE FROM LEGAL_CASE_EMPLOYER
 WHERE legal_case_Seq_num = LegalCaseSeqNum;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
DELETE FROM OUTCOME_ISSUE i
WHERE i.case_outcome_seq_num IN
                 (SELECT case_outcome_seq_num
                       FROM CASE_OUTCOME o
                   WHERE o.legal_case_Seq_num = LegalCaseSeqNum);
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
DELETE FROM OUTCOME_STAFF s
WHERE s.case_outcome_seq_num IN
                 (SELECT case_outcome_seq_num
                       FROM CASE_OUTCOME o
                   WHERE o.legal_case_Seq_num = LegalCaseSeqNum);
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
DELETE FROM CASE_OUTCOME
 WHERE legal_case_Seq_num = LegalCaseSeqNum;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
DELETE FROM CASE_NOTE
 WHERE legal_case_Seq_num = LegalCaseSeqNum;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
DELETE FROM CASE_COST
 WHERE legal_case_Seq_num = LegalCaseSeqNum;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
DELETE FROM SCHEDULE
 WHERE legal_case_Seq_num = LegalCaseSeqNum;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
 DELETE FROM CLAIM_IMAGE_ALERTS_HISTORY
 WHERE legal_case_Seq_num = LegalCaseSeqNum;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
DELETE FROM LEGAL_CASE
 WHERE legal_case_Seq_num = LegalCaseSeqNum;
END;
    v_update_count := SQL%Rowcount;
RETURN v_update_count;

END DeleteLegalCase;

PROCEDURE GetCaseNote(CaseId IN  NUMBER,
                      recordset OUT ref_cursor)
  AS BEGIN
  OPEN recordset FOR
  SELECT
        case_note_seq_num NoteId,
        legal_case_seq_num CaseId,
        NOTE_CATEGORY_CODE CategoryCode,
        text NoteText,
        create_date CreateDate,
        create_user CreateUser,
        update_date UpdateDate,
        update_user UpdateUser
    FROM CASE_NOTE
    WHERE
        legal_case_seq_num = CaseId
        order by Create_date desc;

END GetCaseNote;
/********************************************
** INSERT PROCEDURE FOR TABLE "LEGAL.CASE_NOTE"
** DATE: 06-AUG-03 10:20:33
*********************************************/
Function case_note_insert
(
    noteId          IN CASE_NOTE.case_note_seq_num%TYPE,
    caseId          IN CASE_NOTE.legal_case_seq_num%TYPE,
    categoryCode    IN CASE_NOTE.NOTE_CATEGORY_CODE%TYPE,
    Notetext        IN CASE_NOTE.text%TYPE,
    createDate      IN CASE_NOTE.create_date%TYPE,
    createUser      IN CASE_NOTE.create_user%TYPE)
    RETURN NUMBER
    IS
    v_update_count             NUMBER;
BEGIN
    INSERT INTO LEGAL.CASE_NOTE
    (
        case_note_seq_num,
        legal_case_seq_num,
        NOTE_CATEGORY_CODE,
        text,
        create_date,
        create_user
    )
    VALUES
    (
        noteId,
        caseId,
        categoryCode,
        Notetext,
        createdate,
        createUser
    );
    v_update_count := SQL%Rowcount;
    RETURN v_update_count;
END case_note_insert;
/********************************************
** UPDATE PROCEDURE FOR TABLE "LEGAL.CASE_NOTE"
** DATE: 06-AUG-03 10:21:50
*********************************************/
Function case_note_update
(
    noteId       IN CASE_NOTE.case_note_seq_num%TYPE,
    caseId       IN CASE_NOTE.legal_case_seq_num%TYPE,
    categoryCode IN CASE_NOTE.NOTE_CATEGORY_CODE%TYPE,
    NoteText     IN CASE_NOTE.text%TYPE,
    updateDate   IN CASE_NOTE.update_date%TYPE,
    updateUser   IN CASE_NOTE.update_user%TYPE)
    RETURN NUMBER
    IS
    v_update_count             NUMBER;
BEGIN
    UPDATE LEGAL.CASE_NOTE SET
        legal_case_seq_num = caseId,
        NOTE_CATEGORY_CODE = categoryCode,
        text = Notetext,
        update_date = updateDate,
        update_user = updateUser
    WHERE
        case_note_seq_num = noteId;
    v_update_count := SQL%Rowcount;
    RETURN v_update_count;
END case_note_update;

/********************************************
** DELETE PROCEDURE FOR TABLE "LEGAL.CASE_NOTE"
** DATE: 07-AUG-03 10:21:48
*********************************************/
Function case_note_delete
(
    noteId IN CASE_NOTE.case_note_seq_num%TYPE
) RETURN NUMBER

IS
v_update_count             NUMBER;
BEGIN
    DELETE FROM CASE_NOTE
    WHERE
        case_note_seq_num = noteId;

    v_update_count := SQL%Rowcount;
    RETURN v_update_count;
END case_note_delete;

PROCEDURE GetSAIFAtty(aLegalCaseNum IN  NUMBER,
                      recordset OUT ref_cursor)
  AS BEGIN
  OPEN recordset FOR
  SELECT pro.profile_emplid AS atty_emplid,
       aty.LAST_NAME || ', ' || DECODE(aty.preferred_name, ' ',
       aty.FIRST_NAME,aty.preferred_name) AS atty_name,
       aty.EMAIL_ADDRESS AS atty_email,
       aty.user_id AS aty_mailid,
       DECODE(aty.phone_extension,' ',SUBSTR(aty.PHONE,9), aty.phone_extension) AS aty_extension,
       sec.emplid AS sec_emplid,
       sec.LAST_NAME || ', ' || DECODE(sec.preferred_name, ' ',
       sec.FIRST_NAME,sec.preferred_name) AS sec_name,
       sec.EMAIL_ADDRESS AS sec_email,
       sec.user_id AS sec_mailid,
       DECODE(sec.phone_extension,' ',SUBSTR(sec.PHONE,9), sec.phone_extension) AS sec_extension,
       nvl(aty.phone, '503.285.8525') as AttorneyPhone,
       nvl(ATY.FAX_NUM, '5035849578') as AttorneyFax,
       DECODE(aty.preferred_name, ' ',
       aty.FIRST_NAME,aty.preferred_name)  || ' ' || aty.LAST_NAME  AS AttorneyNameFL
FROM ATTORNEY_PROFILE pro,
     adp_employee aty,
     adp_employee sec
WHERE aty.emplid (+)    = pro.profile_emplid
   AND sec.emplid (+) = pro.secretary_emplid
   AND pro.profile_emplid = get_asgn_atty_id(aLegalCaseNum);

END GetSAIFAtty;

PROCEDURE GetCaseOutcome(LegalCaseID IN  NUMBER,
                      recordset OUT ref_Cursor)
  AS BEGIN
  OPEN recordset FOR
SELECT cot.case_outcome_seq_num CaseOutcomeID,
       cot.legal_case_seq_num LegalCaseID,
       cot.ORDER_TYPE_CODE OrderTypeCode,
       cot.order_signed_date OrderSignedDate,
       cot.order_filed_date OrderFiledDate,
       cot.create_date CreateDate,
       cot.create_user CreateUser,
       cot.update_date UpdateDate,
       cot.update_user UpdateUser,
       NVL(cot.asgn_atty_emplid, '0') AS AsgnAttyEmplid,
       nvl(COT.APPEAL_SEQ_NUM, 0) as appealId
  FROM CASE_OUTCOME cot
 WHERE cot.legal_case_seq_num = LegalCaseID
 ORDER BY cot.order_signed_date;
END GetCaseOutcome;

PROCEDURE GetOutcomeIssue(aLegalCaseNum IN  NUMBER,
                      recordset OUT OutcomeIssueCursor)
  AS BEGIN
  OPEN recordset FOR
SELECT  OCI.outcome_issue_seq_num,
        OCI.case_outcome_seq_num,
        OCI.OUTCOME_ISSUE_CODE,
        OCI.win_loss_flag,
        OCI.fee_amount,
        DECODE(OCI.penalty_flag,'Y',1,0) AS penalty_flag,
        DECODE(OCI.primary_issue_flag,'Y',1,0) AS primary_issue_flag,
        OCI.create_date,
        OCI.create_user,
        OCI.summary_text,
        NVL(OCI.OUTCOME_GROUNDS_CODE,'0') AS OUTCOME_GROUNDS_CODE,
        NVL(OCI.LEGAL_BODY_PART_CODE,'0') AS LEGAL_BODY_PART_CODE,
        NVL(OCI.NATURE_CODE,'0') AS NATURE_CODE,
        OCI.update_date,
        OCI.update_user
    FROM OUTCOME_ISSUE OCI,
         CASE_OUTCOME COT
   WHERE cot.legal_case_seq_num = aLegalCaseNum
     AND OCI.case_outcome_seq_num = COT.case_outcome_seq_num
     ORDER BY OCI.outcome_issue_seq_num;
END GetOutcomeIssue;


PROCEDURE getAljList(aLegalCaseNum IN  NUMBER,
                      recordset OUT ALJListCursor)
  AS BEGIN
  OPEN recordset FOR
SELECT per.legal_person_seq_num, per.last_name,
       typ.LEGAL_PERSON_TYPE_CODE, cod.code_descr,0 AS Selected
 FROM LEGAL_PERSON per,
      LEGAL_PERSON_TYPE typ,
      LEGAL_PERSON_TYPE_CODE cod
WHERE typ.legal_person_seq_num = per.legal_person_seq_num
  AND TRUNC(SYSDATE) BETWEEN typ.eff_date
                         AND NVL(typ.exp_date, '31-dec-2999')
  AND typ.LEGAL_PERSON_TYPE_CODE IN ('ALJ','BOARD', 'FJ')
  AND cod.code = TYP.LEGAL_PERSON_TYPE_CODE
  ORDER BY selected DESC, last_name;
END getAljList;

PROCEDURE GetOutcomeStaff(aLegalCaseNum IN  NUMBER,
                      recordset OUT OutcomeStaffCursor)
  AS BEGIN
  OPEN recordset FOR
SELECT stf.outcome_staff_seq_num,
       stf.case_outcome_seq_num,
       stf.outcome_staff_person_seq_num,
       per.last_name
FROM OUTCOME_STAFF stf,
     LEGAL_PERSON per,
     CASE_OUTCOME cot
WHERE stf.outcome_staff_person_seq_num = per.legal_person_seq_num
 AND  stf.case_outcome_seq_num = cot.case_outcome_seq_num
 AND cot.legal_case_Seq_num = aLegalCaseNum
 ORDER BY stf.outcome_staff_seq_num;
END GetOutcomeStaff;

Function updateCaseOutcome( CaseOutcomeID IN CASE_OUTCOME.case_outcome_seq_num%TYPE,
                             LegalCaseID IN CASE_OUTCOME.legal_case_seq_num%TYPE,
                             OrderTypeCode IN CASE_OUTCOME.ORDER_TYPE_CODE%TYPE,
                             OrderSignedDate IN CASE_OUTCOME.order_signed_date%TYPE := NULL,
                             OrderFiledDate IN CASE_OUTCOME.order_filed_date%TYPE := NULL,
                             UpdateUser IN CASE_OUTCOME.update_user%TYPE := NULL,
                             AsgnAttyEmplid CASE_OUTCOME.asgn_atty_emplid%TYPE := NULL,
                             AppealId CASE_OUTCOME.APPEAL_SEQ_NUM%TYPE := Null)
                             return number
 AS

BEGIN

     UPDATE CASE_OUTCOME SET
        case_outcome_seq_num = CaseOutcomeID,
        legal_case_seq_num = legalcaseId,
        ORDER_TYPE_CODE = ordertypecode,
        order_signed_date = ordersigneddate,
        order_filed_date = orderfileddate,
        update_date = SYSDATE,
        update_user = UpdateUser,
        asgn_atty_emplid = AsgnAttyEmplid,
        appeal_seq_num = appealId
      WHERE
        case_outcome_seq_num = CaseOutcomeID;
 return SQL%Rowcount;
 end updateCaseOutcome;

 Function InsertCaseOutcome( CaseOutcomeID IN CASE_OUTCOME.case_outcome_seq_num%TYPE,
                             LegalCaseID IN CASE_OUTCOME.legal_case_seq_num%TYPE,
                             OrderTypeCode IN CASE_OUTCOME.ORDER_TYPE_CODE%TYPE,
                             OrderSignedDate IN CASE_OUTCOME.order_signed_date%TYPE := NULL,
                             OrderFiledDate IN CASE_OUTCOME.order_filed_date%TYPE := NULL,
                             UpdateUser IN CASE_OUTCOME.update_user%TYPE := NULL,
                             AsgnAttyEmplid CASE_OUTCOME.asgn_atty_emplid%TYPE := NULL,
                             AppealId CASE_OUTCOME.APPEAL_SEQ_NUM%TYPE := Null)
                             return number
 AS
BEGIN
    INSERT INTO CASE_OUTCOME
    (   case_outcome_seq_num,
        legal_case_seq_num,
        ORDER_TYPE_CODE,
        create_date,
        create_user,
        order_signed_date,
        order_filed_date,
        update_date,
        update_user,
        asgn_atty_emplid,
        appeal_seq_num
    )
    VALUES
    (   CaseOutcomeID,
        legalCaseId,
        orderTypeCode,
        SYSDATE,
        UpdateUser,
        orderSignedDate,
        orderFiledDate,
        SYSDATE,
        UpdateUser,
        AsgnAttyEmplid,
        appealId
    );
 return SQL%Rowcount;
END insertCaseOutcome;

PROCEDURE updateOutcomeIssue(outcome_issue_seq_num_in IN OUTCOME_ISSUE.outcome_issue_seq_num%TYPE,
    case_outcome_seq_num_in IN OUTCOME_ISSUE.case_outcome_seq_num%TYPE,
    outcome_issue_code_in IN OUTCOME_ISSUE.OUTCOME_ISSUE_CODE%TYPE,
    win_loss_flag_in IN OUTCOME_ISSUE.win_loss_flag%TYPE,
    fee_amount_in IN OUTCOME_ISSUE.fee_amount%TYPE,
    penalty_flag_in IN OUTCOME_ISSUE.penalty_flag%TYPE,
    primary_issue_flag_in IN OUTCOME_ISSUE.primary_issue_flag%TYPE,
    summary_text_in IN OUTCOME_ISSUE.summary_text%TYPE,
    outcome_grounds_code_in IN OUTCOME_ISSUE.OUTCOME_GROUNDS_CODE%TYPE,
    body_part_code_in IN OUTCOME_ISSUE.LEGAL_BODY_PART_CODE%TYPE,
    nature_code_in IN OUTCOME_ISSUE.NATURE_CODE%TYPE,
    userid_in IN OUTCOME_ISSUE.update_user%TYPE) AS
v_cnt NUMBER := 0;
BEGIN
BEGIN
SELECT COUNT(*)
  INTO v_cnt
  FROM OUTCOME_ISSUE
 WHERE outcome_issue_seq_num = outcome_issue_seq_num_in;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    v_cnt := 0;
END;
BEGIN
  IF v_cnt > 0 THEN
     UPDATE OUTCOME_ISSUE SET
        outcome_issue_seq_num = outcome_issue_seq_num_in,
        case_outcome_seq_num = case_outcome_seq_num_in,
        OUTCOME_ISSUE_CODE = outcome_issue_code_in,
        win_loss_flag = win_loss_flag_in,
        fee_amount = fee_amount_in,
        penalty_flag = penalty_flag_in,
        summary_text = summary_text_in,
        primary_issue_flag = primary_issue_flag_in,
        OUTCOME_GROUNDS_CODE = outcome_grounds_code_in,
        LEGAL_BODY_PART_CODE = body_part_code_in,
        NATURE_CODE = nature_code_in,
        update_date = SYSDATE,
        update_user = userid_in
    WHERE
        outcome_issue_seq_num = outcome_issue_seq_num_in;
  END IF;
END;
BEGIN
IF v_cnt = 0 THEN
INSERT INTO OUTCOME_ISSUE
    (   outcome_issue_seq_num,
        case_outcome_seq_num,
        OUTCOME_ISSUE_CODE,
        win_loss_flag,
        fee_amount,
        penalty_flag,
        summary_text,
        primary_issue_flag,
        create_date,
        create_user,
        OUTCOME_GROUNDS_CODE,
        LEGAL_BODY_PART_CODE,
        NATURE_CODE,
        update_date,
        update_user
    )
    VALUES
    (   outcome_issue_seq_num_in,
        case_outcome_seq_num_in,
        outcome_issue_code_in,
        win_loss_flag_in,
        fee_amount_in,
        penalty_flag_in,
        summary_text_in,
        primary_issue_flag_in,
        SYSDATE,
        userid_in,
        outcome_grounds_code_in,
        body_part_code_in,
        nature_code_in,
        SYSDATE,
        userid_in
    );
END IF;
END;
END updateOutcomeIssue;

PROCEDURE updateOutcomeStaff( outcome_staff_seq_num_in IN OUTCOME_STAFF.outcome_staff_seq_num%TYPE,
                              case_outcome_seq_num_in IN OUTCOME_STAFF.case_outcome_seq_num%TYPE,
                              staff_person_seq_num_in IN OUTCOME_STAFF.outcome_staff_person_seq_num%TYPE )
AS

v_cnt NUMBER := 0;
BEGIN
BEGIN
SELECT COUNT(*)
  INTO v_cnt
  FROM OUTCOME_STAFF
 WHERE outcome_staff_seq_num = outcome_staff_seq_num_in;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    v_cnt := 0;
END;
BEGIN
IF v_cnt > 0 THEN
   UPDATE OUTCOME_STAFF SET
        outcome_staff_seq_num = outcome_staff_seq_num_in,
        case_outcome_seq_num = case_outcome_seq_num_in,
        outcome_staff_person_seq_num = staff_person_seq_num_in
    WHERE
        outcome_staff_seq_num = outcome_staff_seq_num_in;
END IF;
END;
BEGIN
IF v_cnt = 0 THEN
   INSERT INTO LEGAL.OUTCOME_STAFF
    (  outcome_staff_seq_num,
       case_outcome_seq_num,
       outcome_staff_person_seq_num
    )
    VALUES
    (  outcome_staff_seq_num_in,
       case_outcome_seq_num_in,
       staff_person_seq_num_in
    );
END IF;
END;

END updateOutcomeStaff;

FUNCTION DeleteCaseOutcome(case_outcome_seq_num_in IN CASE_OUTCOME.case_outcome_seq_num%TYPE) return number
AS
BEGIN

--Delete child issues
BEGIN
DELETE FROM OUTCOME_ISSUE
 WHERE CASE_OUTCOME_SEQ_NUM = case_outcome_seq_num_in;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

--Delete child staff
BEGIN
DELETE FROM OUTCOME_STAFF
 WHERE CASE_OUTCOME_SEQ_NUM = case_outcome_seq_num_in;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

--Now delete the outcome
BEGIN
DELETE FROM CASE_OUTCOME
 WHERE CASE_OUTCOME_SEQ_NUM = case_outcome_seq_num_in;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;
RETURN SQL%Rowcount;
END DeleteCaseOutcome;


PROCEDURE DeleteOutcomeIssue(outcome_issue_seq_num_in IN OUTCOME_ISSUE.outcome_issue_seq_num%TYPE)
AS
BEGIN
DELETE FROM OUTCOME_ISSUE
 WHERE outcome_issue_seq_num = outcome_issue_seq_num_in;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END DeleteOutcomeIssue;

PROCEDURE DeleteOutcomeStaff(outcome_staff_seq_num_in IN OUTCOME_STAFF.outcome_staff_seq_num%TYPE) AS
BEGIN
DELETE FROM OUTCOME_STAFF
 WHERE outcome_staff_seq_num = outcome_staff_seq_num_in;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END DeleteOutcomeStaff;
-----
PROCEDURE SetFinalOutcome( legal_case_seq_num_in IN LEGAL_CASE.legal_case_seq_num%TYPE) AS
v_final_seq NUMBER := 0;
BEGIN
-- Get the Seq Num of the Final Outcome
BEGIN
SELECT MAX(case_outcome_seq_num)
  INTO v_final_seq
  FROM CASE_OUTCOME
  WHERE legal_case_seq_num = legal_case_seq_num_in
    AND order_signed_date  =
        (SELECT MAX(order_signed_date)
           FROM CASE_OUTCOME
           WHERE legal_case_seq_num = legal_case_seq_num_in);
           EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;
-- Set the Final Flag
BEGIN
  UPDATE CASE_OUTCOME
     SET final_order_flag = 'N'
   WHERE legal_case_seq_num = legal_case_seq_num_in
     AND case_outcome_seq_num <> v_final_seq;
   UPDATE CASE_OUTCOME
     SET final_order_flag = 'Y'
   WHERE legal_case_Seq_num =  legal_case_seq_num_in
     AND case_outcome_seq_num = v_final_seq;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;
END SetFinalOutcome;


-----


PROCEDURE GetLegalCaseEmployer(aLegalCaseNum IN  NUMBER,
                      recordset OUT LegalCaseEmployerCursor)
AS BEGIN OPEN recordset FOR
 SELECT legal_case_employer_seq_num,
        legal_case_seq_num,
        employer_other_firm_seq_num,
        DECODE(employer_discovery_flag,'Y','true','false') AS employer_discovery_flag,
        DECODE(attorney_discovery_flag, 'Y','true','false') AS attorney_discovery_flag,
        attorney_other_firm_seq_num,
        attorney_legal_person_seq_num
    FROM LEGAL_CASE_EMPLOYER
   WHERE legal_case_seq_num = aLegalCaseNum;

END getLegalCaseEmployer;

/********************************************
** INSERT PROCEDURE FOR TABLE "LEGAL.LEGAL_CASE_EMPLOYER"
** DATE: 03-SEP-03 08:41:55
*********************************************/
PROCEDURE legal_case_employer_insert
(
    legal_case_employer_seq_num_in IN LEGAL_CASE_EMPLOYER.legal_case_employer_seq_num%TYPE,
    legal_case_seq_num_in IN LEGAL_CASE_EMPLOYER.legal_case_seq_num%TYPE,
    other_firm_seq_num_in IN LEGAL_CASE_EMPLOYER.employer_other_firm_seq_num%TYPE,
    discovery_flag_in IN LEGAL_CASE_EMPLOYER.employer_discovery_flag%TYPE,
    attorney_discovery_flag_in IN LEGAL_CASE_EMPLOYER.attorney_discovery_flag%TYPE,
    atty_other_firm_seq_num_in IN LEGAL_CASE_EMPLOYER.attorney_other_firm_seq_num%TYPE,
    atty_legal_person_seq_num_in IN LEGAL_CASE_EMPLOYER.attorney_legal_person_seq_num%TYPE
)
IS
BEGIN
    INSERT INTO LEGAL_CASE_EMPLOYER
    (
        legal_case_employer_seq_num,
        legal_case_seq_num,
        employer_other_firm_seq_num,
        employer_discovery_flag,
        attorney_discovery_flag,
        attorney_other_firm_seq_num,
        attorney_legal_person_seq_num
    )
    VALUES
    (
        legal_case_employer_seq_num_in,
        legal_case_seq_num_in,
        other_firm_seq_num_in,
        discovery_flag_in,
        attorney_discovery_flag_in,
        atty_other_firm_seq_num_in,
        atty_legal_person_seq_num_in
    );
END legal_case_employer_insert;

/********************************************
** UPDATE PROCEDURE FOR TABLE "LEGAL.LEGAL_CASE_EMPLOYER"
** DATE: 03-SEP-03 08:43:30
*********************************************/
PROCEDURE legal_case_employer_update
(
    legal_case_employer_seq_num_in IN LEGAL_CASE_EMPLOYER.legal_case_employer_seq_num%TYPE,
    legal_case_seq_num_in IN LEGAL_CASE_EMPLOYER.legal_case_seq_num%TYPE,
    other_firm_seq_num_in IN LEGAL_CASE_EMPLOYER.employer_other_firm_seq_num%TYPE,
    discovery_flag_in IN LEGAL_CASE_EMPLOYER.employer_discovery_flag%TYPE,
    attorney_discovery_flag_in IN LEGAL_CASE_EMPLOYER.attorney_discovery_flag%TYPE,
    atty_other_firm_seq_num_in IN LEGAL_CASE_EMPLOYER.attorney_other_firm_seq_num%TYPE,
    atty_legal_person_seq_num_in IN LEGAL_CASE_EMPLOYER.attorney_legal_person_seq_num%TYPE
)
IS
BEGIN
    UPDATE LEGAL.LEGAL_CASE_EMPLOYER SET
        legal_case_seq_num = legal_case_seq_num_in,
        employer_other_firm_seq_num = other_firm_seq_num_in,
        employer_discovery_flag = discovery_flag_in,
        attorney_discovery_flag = attorney_discovery_flag_in,
        attorney_other_firm_seq_num = atty_other_firm_seq_num_in,
        attorney_legal_person_seq_num = atty_legal_person_seq_num_in
    WHERE
        legal_case_employer_seq_num = legal_case_employer_seq_num_in;
END legal_case_employer_update;

-- Get the Attorney Address for Printing on Letters
-- IF an attorney exists for the case, get the address from provider
-- Otherwise get the worker address
PROCEDURE GetWorkerAtty(recordset OUT ref_cursor, inFirmLocID IN NUMBER, inAttyLocID IN NUMBER)
AS
v_addr_cur Pk82002_Provider.address_cursor;
v_addr_rec Pk82002_Provider.address_rec;

outFirmName VARCHAR2(100):= '';
outAttyName VARCHAR2(100):= '';
outAddr1 VARCHAR2(100):= '';
outAddr2 VARCHAR2(100):= '';
outAddr3 VARCHAR2(100):= '';
outAddr4 VARCHAR2(100):= '';
outPhone VARCHAR2(100):= '';
BEGIN

IF inFirmLocID > 0 THEN
   -- Get the Firm Address
   Pk82002_Provider.getLocByID(inFirmLocID,'COR',v_addr_cur);
   LOOP
    BEGIN
            FETCH v_addr_cur INTO v_addr_rec;
            EXIT WHEN v_addr_cur%NOTFOUND;
            outFirmName := Sp79013_Initcap_Name(v_addr_rec.location_name);
            outAddr1 := v_addr_rec.addr_line1;
            outAddr2 := v_addr_rec.addr_line2;
            outAddr3 := v_addr_rec.addr_line3;
            outAddr4 := v_addr_rec.addr_line4;
            outPhone := v_addr_rec.phone_num;
            IF v_addr_rec.foreign_addr_flag <> 'Y' THEN
                outAddr4 := v_addr_rec.city_st;
            END IF;
     END;
   END LOOP;
   IF inAttyLocID > 0 THEN
      BEGIN
        outAttyName := Sp79013_Initcap_Name(Pk82002_Provider.getProviderName(inAttyLocID));
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          outAttyName := '';
      END;
   END IF;
END IF;

OPEN recordset FOR
SELECT /*+ FIRST_ROWS */
            outFirmName FirmName,
            outAttyName AttyName,
            outAddr1 Addr1,
            outAddr2 Addr2,
            outAddr3 Addr3,
            outAddr4 Addr4,
            outPhone Phone
            FROM DUAL;
END GetWorkerAtty;

PROCEDURE GetAttyLeaveReport(inStartDate IN DATE,
                             inStopDate IN DATE,
                             recordset OUT AttyLeaveCursor)
AS BEGIN OPEN recordset FOR
SELECT /*+ FIRST_ROWS */  schedule_emplid,
       MAX(RTRIM(E.last_name) || ', ' || RTRIM(DECODE(NVL(E.preferred_name,' '), ' ', E.FIRST_NAME,E.preferred_name))) AS ATTY_NAME,
       SUM(DECODE(ATTORNEY_SCHEDULE_TYPE_CODE,'VAC', Sp72219_Day_Count(GREATEST(inStartDate,eff_Date),LEAST(inStopDate, exp_date))+ 1, 0)) AS vac_days,
       SUM(DECODE(ATTORNEY_SCHEDULE_TYPE_CODE,'LEAVE', Sp72219_Day_Count(GREATEST(inStartDate,eff_Date),LEAST(inStopDate, exp_date))+ 1, 0)) AS med_days
       FROM ATTORNEY_SCHEDULE s, adp_employee E
       WHERE E.emplid = s.schedule_emplid
       AND  ATTORNEY_SCHEDULE_TYPE_CODE IN ('VAC', 'LEAVE')
       AND  DURATION_CODE = 'FULL'
       AND ( (TRUNC(eff_Date) BETWEEN inStartDate AND inStopDate)
       OR    (TRUNC(exp_date) BETWEEN inStartDate AND inStopDate)
       OR    (TRUNC(eff_Date) < inStartDate AND TRUNC(exp_date) > inStopDate))
       GROUP BY schedule_emplid
       ORDER BY 2;
END GetAttyLeaveReport;

PROCEDURE alertlegalcase(
   inClaimId   IN   NUMBER
, outAlertFlag OUT VARCHAR2)

IS
   v_cases    NUMBER      := 0;
   v_return   VARCHAR2(1) := 'N';

   CURSOR check_legal_cases
   IS
      SELECT COUNT(*)
        FROM LEGAL_CASE lc
       WHERE lc.claim_image_alerts_flag = 'Y'
         AND lc.claim_id = inClaimId;
BEGIN
   IF NOT check_legal_cases%ISOPEN THEN
      OPEN check_legal_cases;
   END IF;

   FETCH check_legal_cases
    INTO v_cases;

   IF check_legal_cases%FOUND THEN
      IF v_cases > 0 THEN
         v_return                   := 'Y';
      END IF;
   END IF;

   IF check_legal_cases%ISOPEN THEN
      CLOSE check_legal_cases;
   END IF;

   outAlertFlag := v_return;
END alertlegalcase;

PROCEDURE SelectImageAlertsHist(inCaseSeqNum IN NUMBER, recordset OUT ref_cursor) AS
BEGIN
OPEN recordset FOR
          SELECT claim_image_alrt_hist_seq_num, legal_case_Seq_num, claim_image_alerts_flag, update_date, update_user
              FROM CLAIM_IMAGE_ALERTS_HISTORY
          WHERE legal_case_Seq_num = inCaseSeqNum
    ORDER BY update_Date DESC;
END SelectImageAlertsHist;

PROCEDURE SelectLegalEmployees(recordset OUT ref_cursor) AS
BEGIN
OPEN recordset FOR
    SELECT    LAST_NAME  || ', '  ||
                 DECODE (NVL (preferred_name, ' '), ' ', FIRST_NAME, preferred_name)  AS EMPLOYEE_NAME,
         USER_ID,  EMPLID,  DESCR,  NVL (ATTORNEY_TYPE_CODE, 'NA') AS ATTORNEY_TYPE_CODE
    FROM    ADP_EMPLOYEE A
         LEFT OUTER JOIN ATTORNEY_PROFILE P ON A.EMPLID = P.PROFILE_EMPLID
   WHERE (DEPTID = '005201'
        OR  (DEPTID = '001101' AND UPPER (DESCR) LIKE '%LEGAL%'))
         AND EMPL_STATUS = 'A'
ORDER BY EMPLOYEE_NAME;

END SelectLegalEmployees;

PROCEDURE getPolicyAddresses(inPolicyNum IN NUMBER,
       recordset OUT ref_cursor) AS
       BEGIN
OPEN recordset FOR
select   loc_seq_num as PolicyLocSeqNum, LOC.location_code as code,
  loc.primary_location_flag as primaryFlag
 ,LCD.SHORT_CODE_DESCR as PolicyLocCode
 from location loc
 left outer join location_code lcd on LOC.LOCATION_CODE = LCD.CODE
 where policy_num =  inPolicyNum
 and to_char(sysdate, 'yyyy/mm/dd') >= to_char(loc_eff_date,'yyyy/mm/dd')
 and to_char(sysdate, 'yyyy/mm/dd') <= nvl(to_char(loc_exp_date,'yyyy/mm/dd'), to_char(sysdate,'yyyy/mm/dd'))
 order by decode(LOC.LOCATION_CODE,'CLM','1','POL','2','BUS','3','4'), primary_location_flag desc;
 END getPolicyAddresses;
END;
```

</details>

> **Note:** Statements marked with * were inferred from code structure, naming conventions, or
> contextual evidence where direct confirmation was not available in source artifacts. Statements
> marked with ** could not be confirmed; treat as unknown until verified.

## Related Documentation

- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)

## Sources

- Oracle package specification and body: `WCIS_DBA.pk80001_legalnet` (retrieved via `legends oracle query --owner SAIF --name pk80001_legalnet` on 2026-08-31)
- Legacy calling-class mapping cross-checked against `Claims.BOF.Web.LegalMaintenance` VB.NET/.NET class usage